Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Is there a specific problem you are having with that script?
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
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
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
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
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
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 *;
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 *;
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