Sudeshna Sarkar New User Joined: 11 Sep 2008 Posts: 29 Location: Kolkata
I had a client interview, and I was just asked to identify and describe the operation happening in the following two queiries. CAn you please throw some light as to what is happeneing here?
Case 6 (DB2):
FROM SAFE_EQUITY_TRADES AS TB1
, SAFE_CP AS TB2
, SAFE_CP AS TB3
AND TB1.CP_ID = TB2.CP_ID
AND TB1.OFFFSET_CP_ID = TB3.CP_ID
AND TB3.CP_ID = TB1.OFFFSET_CP_ID
AND TB2.CP_ID = TB1.CP_ID
AND TB2.HOUSE_IND <> 'Y'
AND TB3.HOUSE_IND <> 'Y'
AND TB1.CP_ID <> 0
AND TB1.OFFFSET_CP_ID <> 0
Case 7 (DB2):
SELECT RTRIM(PRM_CP_NAME) || '|' ||
RTRIM(TR_PRODUCT) || '|' ||
SUBSTR(TR_TRADE_DT,5,2) || '/' ||
SUBSTR(TR_TRADE_DT,7,2) || '/' ||
SUBSTR(TR_TRADE_DT,1,4) || '|' ||
RTRIM(CHAR(INT(TR_PE*-1)))||SUBSTR(CHAR(ROUND(TR_PE*-1,2)),30,3) || '|' ||
RTRIM(CHAR(INT(TR_PRINCIPAL/1000)))||SUBSTR(CHAR(ROUND(TR_PRINCIPAL/1000,2)),22,3) || '|' ||
Case 7 (DB2):
'CREATE SYNONYM '||X1.NAME,' FOR BEADB2T.'||X1.NAME||';'
FROM SYSIBM.SYSTABLES X1
WHERE X1.NAME LIKE 'SAFE%'
AND X1.NAME NOT IN ( SELECT X3.NAME
FROM SYSIBM.SYSTABLES X3
WHERE X3.CREATOR = USER)
AND X1.NAME IN ( SELECT X4.STNAME
FROM SYSIBM.SYSTABAUTH X4
WHERE X4.GRANTEE = 'BEADB2T')
AND NOT EXISTS
FROM SYSIBM.SYSSYNONYMS X2
WHERE X1.NAME = X2.TBNAME AND
X2.CREATOR = USER)
ashimer Active Member Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
1st one is an inner join
2nd no idea what you have mentioned.
3rd - not in and in are subqueries while not exists is correlated subquery.
This should have been posted in interview questions forum.
All times are GMT + 6 Hours