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;

[jpapador]

Is there a specific problem you are having with that script?

[#3]

Hey Daniel,

I think where exists() will work for you in this case(you can use it in the preload ).

Example:

Table1:

Load a,b,c,*;

SQL Select *;

Inner Join

Load a,b,c,*

Where exists(a) and exists(b) and exists(c);

SQL Select *;

Thanks

AJ

[danielnevitt]

Hi jpapador,

The script load fails with the message stating:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "B_PRICE1": invalid identifier

SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '12 Mar 2014'
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

Regards,

Daniel

[danielnevitt]

Hi Ajay,

Thanks for your reply.

With regards to the where exists() function.  Will this return only values where the OFFICE_CD, DESK_CD,CO_CD, CONTRACT_DT etc are the same in the second table as the are in the first table (on a line by line basis)?

Regards,

Daniel

[#6]

Yes. Its on a line by line basis. Only if all those exists on the same line in Table1 then that record will be brought in from Table2.

Thanks

[danielnevitt]

Hi Ajay,

Thanks, that's really helpful.

Last question.  If I specify that Table2 should only show 'Sell' trades (AND BS_IND IN ('Sell')) after the where exists statement that you suggest.  Will this still work?

Thanks

Daniel

[#8]

Sure something like this should work:

Inner Join

Load a,b,c,*

Where exists(a) and exists(b) and exists(c) and BS_IND='Sell' ;

SQL Select *;

[#9]

A small correction to what I said. Instead of doing where exists individually please create a key out of it and try it.

Table1:

Load autonumberhash128(a,b,c) as Key,*;

SQL Select *;

Inner Join

Load *

Where exists(Key,Key1);

Load Autonumberhash128(a,b,c) as Key1,*;

SQL Select *;

[danielnevitt]

Hi Ajay,

Sorry I'm now slightly confused how to code this.

How should I write the statement to only join on the 5 fields stated above, but also still show the other fields.  I also want to restrict the data to (as per the SQL load):

WHERE TRADE_TYPE in ('Future')

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

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

Thanks,

Daniel