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
 

 

Need info on SELECT statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Tue Jun 23, 2015 11:02 pm    Post subject: Need info on SELECT statement
Reply with quote

Hi,

I need one small info regarding below mentioned SELECT statement. While checking an old module I saw below given SQL statement.

In the below query we have 2 queries. If I run only the subquery I get one column and that is blank. But when I run the complete query I get one column with some number in it.

I know it could be difficult to guess without seeing the data on which the query will run. But I just want to understand what this query is actually doing. The subquery has "SELECT '' FROM", what does this query means and how the outer query gets value from the inner query through "AND EXISTS"

[I am aware of EXISTS verb in SQL but how the same is working here that I just want to know]



Code:
SELECT COUNT(*)
          INTO   :XXXXXX
          FROM   <TABLE_NAME1>
          WHERE  ID_MODEL   = :XXXXXX
          AND      CD_COLOUR  = :XXXXXX
          AND      CD_SIZE    = :XXXXXX
          AND EXISTS
          (SELECT '' FROM <TABLE_NAME2>
           WHERE    ID_MESSAGE = :XXXXXX
            AND (((SUBSTR(LX_EVENT,1,4) < SUBSTR(CHAR(TS_UPDATE),1,4)
            OR    SUBSTR(LX_EVENT,5,2)  < SUBSTR(CHAR(TS_UPDATE),6,2)
            OR    SUBSTR(LX_EVENT,7,2)  < SUBSTR(CHAR(TS_UPDATE),9,2))
           AND    DATE(TS_UPDATE)       = CURRENT DATE
           AND   (HOUR(TS_UPDATE)       - :XXXXXX)
                                           > 0
           AND    CD_MESSAGE_STATUS        = 'A')
           OR     CD_MESSAGE_STATUS        = 'P'
           OR    (CD_MESSAGE_STATUS        = 'E'))) ;


If you can redirect me to some good link where I can get detail info on this that also will be a great help.

Thanks
Subrata
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Tue Jun 23, 2015 11:15 pm    Post subject: Reply to: Need info on SELECT statement
Reply with quote

The Exists works like an 'If' condition here.

The subquery returns ' ' (spaces) if the condition in the subquery is met. The equivalent to cobol would be like; If conditions in subquery is true then run the main query.

If the where conditions are not satisfied in subquery then that means that the condition in main query is also not satisfied and a sqlcode of 100 is returned from the main query i.e. Not found.

.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Jun 24, 2015 1:59 pm    Post subject:
Reply with quote

Hi RahulG,

Thanks for the info. Yes I understand that now. Would it be possible for you to redirect me to some weblink, where I can go and read more about this and this kind of details?

Thanks
Subrata
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Wed Jun 24, 2015 7:00 pm    Post subject: Reply to: Need info on SELECT statement
Reply with quote

Subrata,

You may look at:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNAPK10/1.4.2.4?DT=20070123230158

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNSQK10/2.23.5?DT=20070125023435

for understanding EXISTS predicate.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Jun 25, 2015 11:11 am    Post subject:
Reply with quote

Hi RahulG,

Thanks for these 2 links. Sure I will check.

Thanks
Subrata
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Jun 26, 2015 9:24 am    Post subject:
Reply with quote

Hi Rahul,

Not sure what is happening. But for me both the links are not working. Anyway EXISTS predicates I am already aware of and my query was related to that "SELECT '' FROM" thing. As you have already explained me the same. So it's ok..

Thanks
Subrata
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Fri Jun 26, 2015 11:38 am    Post subject: Reply to: Need info on SELECT statement
Reply with quote

subratarec,

I've fixed the formatting of the links so that you can just click on them. Previously you could still copy/paste into your browser and get to them that way. Was that too much effort for you?
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Jun 26, 2015 9:33 pm    Post subject:
Reply with quote

Hi Bill,


Nope icon_smile.gif but thanks for formatting the link

Thanks
Subrata
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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