Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query Identification- nested, subquery, correlated subquery

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
Sudeshna Sarkar

New User


Joined: 11 Sep 2008
Posts: 29
Location: Kolkata

PostPosted: Tue Aug 25, 2009 1:24 pm    Post subject: Query Identification- nested, subquery, correlated subquery
Reply with quote

Hello Friends,

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):
=========


Code:
SELECT     ACCOUNT
          ,OFFSET_ACCOUNT
          ,BUY_SELL
          ,PRINCIPAL
      ,TB1.CP_ID
          ,OFFFSET_CP_ID

FROM   SAFE_EQUITY_TRADES AS TB1
     , SAFE_CP            AS TB2
     , SAFE_CP            AS TB3

WHERE
      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):
========



Code:
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):



Code:
SELECT DISTINCT
         '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
                          (SELECT  *
                             FROM  SYSIBM.SYSSYNONYMS X2
                            WHERE  X1.NAME = X2.TBNAME      AND
                                   X2.CREATOR = USER)


Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 25, 2009 1:30 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us