Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm
No new posts Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am
No new posts reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us