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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

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