SQL Table Join

[danielnevitt]

Hi,

I would really appreciate some help with the following SQL load statement.

I am trying to join the first table with the second table when OFFICE_CD,DESK_CD, CONTRACT_DT,PRICE1 and CO_CD equal an entry in the opposite table.  The reason for spliting the data into two tables is to ensure that a buy is match with a sell.

Is there an easier way to code this?  If not, is there something obvious that is incorrect with my code.

Thanks for any help.

Regards,

Daniel

vToday1 = Date(Today(),'DD MMM YYYY');
ODBC CONNECT32 TO [Tempest_DCF_Prod;DBQ=DCFDWP1] (XUserId is DCCXQANIMaXKXZVMSDRB, XPassword is YVbVPSNGWLMGWZMOaLYA);
//-------- Start Multiple Select Statements ------
LOAD *,
DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,
BS_IND AS A_BS_IND,
CMDTY_CD AS A_CMDTY_CD,
CO_CD AS A_CO_CD,
CONTRACT_DT AS A_CONTRACT_DT,
CONTRACT_QTY AS A_CONTRACT_QTY,
DESK_CD AS A_DESK_CD,
EXPIRE_DT AS A_EXPIRE_DT,
OFFICE_CD AS A_OFFICE_CD,
PERIOD_CD AS A_PERIOD_CD,
PRICE1 AS A_PRICE1,
TRADE_NUM AS A_TRADE_NUM,
TRADE_STATUS_IND AS A_TRADE_STATUS_IND,
TRADE_TYPE AS A_TRADE_TYPE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '$(vToday1)'
AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND   BS_IND IN ('Buy');

INNER JOIN LOAD *,
DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,
BS_IND AS B_BS_IND,
CMDTY_CD AS B_CMDTY_CD,
CO_CD AS B_CO_CD,
CONTRACT_DT AS B_CONTRACT_DT,
CONTRACT_QTY AS B_CONTRACT_QTY,
DESK_CD AS B_DESK_CD,
EXPIRE_DT AS B_EXPIRE_DT,
OFFICE_CD AS B_OFFICE_CD,
PERIOD_CD AS B_PERIOD_CD,
PRICE1 AS B_PRICE1,
TRADE_NUM AS B_TRADE_NUM,
TRADE_STATUS_IND AS B_TRADE_STATUS_IND,
TRADE_TYPE AS B_TRADE_TYPE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '$(vToday1)'
AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND   BS_IND IN ('Sell')
AND   A_DESK_CD = B_DESK_CD
AND   A_OFFICE_CD = B_OFFICE_CD
AND   A_CO_CD = B_CO_CD
AND   A_CONTRACT_DT = B_CONTRACT_DT
AND   A_PRICE1 = B_PRICE1;

[#2]

Ahh.... now I see what you want. In this case you dont have to use exists. Just a Left Join. Like below:

vToday1 = Date(Today(),'DD MMM YYYY');

ODBC CONNECT32 TO [Tempest_DCF_Prod;DBQ=DCFDWP1] (XUserId is DCCXQANIMaXKXZVMSDRB, XPassword is YVbVPSNGWLMGWZMOaLYA);

//-------- Start Multiple Select Statements ------

LOAD *,

DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,

BS_IND AS A_BS_IND,

CMDTY_CD AS A_CMDTY_CD,

CO_CD AS A_CO_CD,

CONTRACT_DT AS A_CONTRACT_DT,

CONTRACT_QTY AS A_CONTRACT_QTY,

DESK_CD AS A_DESK_CD,

EXPIRE_DT AS A_EXPIRE_DT,

OFFICE_CD AS A_OFFICE_CD,

PERIOD_CD AS A_PERIOD_CD,

PRICE1 AS A_PRICE1,

TRADE_NUM AS A_TRADE_NUM,

TRADE_STATUS_IND AS A_TRADE_STATUS_IND,

TRADE_TYPE AS A_TRADE_TYPE;

SQL SELECT *

FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"

WHERE TRADE_TYPE in ('Future')

AND   trunc(EXPIRE_DT) > '$(vToday1)'

AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')

AND   BS_IND IN ('Buy');

Left JOIN

LOAD *,

DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,

BS_IND AS B_BS_IND,

CMDTY_CD AS B_CMDTY_CD,

CO_CD AS B_CO_CD,

CONTRACT_DT AS B_CONTRACT_DT,

CONTRACT_QTY AS B_CONTRACT_QTY,

DESK_CD AS B_DESK_CD,

EXPIRE_DT AS B_EXPIRE_DT,

OFFICE_CD AS B_OFFICE_CD,

PERIOD_CD AS B_PERIOD_CD,

PRICE1 AS B_PRICE1,

TRADE_NUM AS B_TRADE_NUM,

TRADE_STATUS_IND AS B_TRADE_STATUS_IND,

TRADE_TYPE AS B_TRADE_TYPE;

SQL SELECT *

FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"

WHERE TRADE_TYPE in ('Future')

AND   trunc(EXPIRE_DT) > '$(vToday1)'

AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')

AND   BS_IND IN ('Sell');

[danielnevitt]

Thanks Ajay.

Is there a way to only show the records in both tables where the JOIN_REFERENCE matches?

My understanding is that the above will only join table 2 data to table 1 where the JOIN_REFERENCE is the same.

Regards,

Daniel

[maxgro]

inner join instead of left join

from help online

If used before join it specifies that an inner join should be used. The resulting table will thus only contain combinations of field values from the raw data tables where the linking field values are represented in both tables.

[#5]

Ok in that case the same code as above. Just change left join to inner join.

You don't need a separate IN Sql command in the second table for those 5 fields.

Since they are part of the key, when you do an inner join they will automatically join with each other  based on common JOIN_REFERENCE field values.

[danielnevitt]

Thanks for all your help.

Regards,

Daniel

[danielnevitt]

Hi,

I have tried to run the script (see below) this morning and it is not producing any results.

It would be great if someone could help by answering the following questions:

1) Is there a way of restricting the load from both tables to only the new fields I have created?

2) Is the statement I have written the best way to achieve trying to show where a buy and a sell are matched on specific fields?

Any help will be much appreciated.

Regards,

Daniel

LET vToday1 = Date(Today(),'DD MMM YYYY');

LOAD *,
BS_IND AS A_BS_IND,
CMDTY_CD AS A_CMDTY_CD,
CO_CD AS A_CO_CD,
CONTRACT_DT AS A_CONTRACT_DT,
CONTRACT_QTY AS A_CONTRACT_QTY,
DESK_CD AS A_DESK_CD,
EXPIRE_DT AS A_EXPIRE_DT,
OFFICE_CD AS A_OFFICE_CD,
PERIOD_CD AS A_PERIOD_CD,
PRICE1 AS A_PRICE1,
TRADE_NUM AS A_TRADE_NUM,
TRADE_STATUS_IND AS A_TRADE_STATUS_IND,
TRADE_TYPE AS A_TRADE_TYPE,
DESK_CD&OFFICE_CD&CONTRACT_DT&CO_CD AS JOIN_REFERENCE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND  trunc(EXPIRE_DT) > '$(vToday1)'
AND  TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND  BS_IND IN ('Buy');

INNER JOIN
LOAD *,
BS_IND AS B_BS_IND,
CMDTY_CD AS B_CMDTY_CD,
CO_CD AS B_CO_CD,
CONTRACT_DT AS B_CONTRACT_DT,
CONTRACT_QTY AS B_CONTRACT_QTY,
DESK_CD AS B_DESK_CD,
EXPIRE_DT AS B_EXPIRE_DT,
OFFICE_CD AS B_OFFICE_CD,
PERIOD_CD AS B_PERIOD_CD,
PRICE1 AS B_PRICE1,
TRADE_NUM AS B_TRADE_NUM,
TRADE_STATUS_IND AS B_TRADE_STATUS_IND,
TRADE_TYPE AS B_TRADE_TYPE,
DESK_CD&OFFICE_CD&CONTRACT_DT&CO_CD AS JOIN_REFERENCE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND  trunc(EXPIRE_DT) > '$(vToday1)'
AND  TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND  BS_IND IN ('Sell');