Can you JOIN two RESIDENT tables?

[Anonymous]

Hello my friends.  I have been trying to get a query to work this morning and it has not been easy.  I was wondering if the problem may be that I am trying to JOIN two resident tables.  Is this even possible?  Thanks.

:
LOAD     PRODUCTLINE            AS A_PRODUCTLINE,
        If([PRODUCTLINE]='01','' & PURCHASEORDER & '',
        If([PRODUCTLINE]='02','0200000' & Right(Left(PURCHASEORDER,12),5) & '02',
        If([PRODUCTLINE]='03', Right(NEW_PO,5),
        If([PRODUCTLINE]='04','' & PURCHASEORDER & '',
        If([PRODUCTLINE]='08','0800000' & Right(NEW_PO,5) & '08'))))) AS A_PURCHASE_ORDER,
        SYSTEM
RESIDENT NEW_PO;


LEFT JOIN (A)

:
LOAD If(IsNull([HADI]),Today(),[HADI]) AS A_START_SHIP_DATE,
     SYSTEM
RESIDENT IPPOHDR_DATA; 

[swuehl]

I think you should remove the

:

line, after the join, there will be only table (and your two resident source tables), so no need to label your second table.

Your tables will be joined using the SYSTEM field as key, so check that values do match.

Hope this helps,

Stefan

[#3]

There is no problem to join two resident tables, but once they are joined, you'll have all the records in one table, so you have to drop the other table. In the example, you have table A, but once you have it you must:

DROP TABLE NEW_PO.

Then, you left join A with Resident IPPOHDR_DATA. Here, you don't need to put : before the LOAD, because the data will be automatically concatenated to A. After this, you can Drop the table IPPOHDR_DATA;

Remember that if none of your fields has the same name, the Left join will result none of your fields joined.