|
View previous topic :: View next topic
|
| Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi RahulG,
Thanks for these 2 links. Sure I will check.
Thanks
Subrata |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi Bill,
Nope  but thanks for formatting the link
Thanks
Subrata |
|
| Back to top |
|
|
|
|
