creating a new table

[Anonymous]

Hello 🙂

I have two tables with invoice data:

Invoice:

LOAD

  IV,

  PART, //PAYPART

QUANT as PAYQUANT

InvoiceDetailed:

LOAD

    IV,

    PART, //ACTUALPART

    QUANT as ACTUALQUANT

for example:

Invoice table:

IV  PART    PAYQUANT

19    XXX   15

19    ZZZ    13

InvoiceDetailed table:

IV    PART    ACTUALQUANT

19    XXX    15

19    YYY    22

What I need is to have a final table with all Possibilities:

IV    PART    PAYQUANT    ACTUALQUANT

19     XXX     15                    15

19     ZZZ      13                     0

19     YYY      0                      22

I would appreiciate your help in finding a solution.

[Anil_Babu_Samineni]

Hahaha, I shocked. Some what i gave good input to you. Now, you can close this thread by flag Correct Answer

[maxgro]

maybe you have a different name for PART field in the 2 source tables, PAYPART and ACTUALPART;

you should rename PAYPART as PART and ACTUALPART as PART if you want the join on 2 fields: the join work if the fields has the same names

[Invoice Table]:

LOAD

    IV,                                        // join field

    PAYPART as PART,              // join field     

    QUANT as PAYQUANT;

SQL SELECT

    IV,

    PAYPART,

    QUANT

FROM

    .............

    ;

JOIN ([Invoice Table])

LOAD

    IV,                                            // join field

    ACTUALPART as PART,            // join field

    QUANT as ACTUALQUANT

SQL SELECT

    IV,

    ACTUALPART,

    QUANT

FROM

    .............

    ;