View previous topic :: View next topic
|
Author |
Message |
VivekKhanna
New User
Joined: 09 Feb 2009 Posts: 57 Location: India
|
|
|
|
Hi,
I need an SQL, which could provide me with following output
1. 'Y' when WHERE conditions gets satisfied
2. 'N' when WHERE conditions doesn't get satisfied.
I just need to check, if the corresponding information is present in the table or not. For example, I need to check, if an Employee# exists in a EMPLOYEE table or not. So my Employee# will go into WHERE condition. CASE statement is prohibited, as it creates performance issues. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
CASE statement is prohibited, as it creates performance issues.
|
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
if it is imbedded sql, you can use cobol code to provide you with the y or n based on sqlcode.
otherwise, I do not see how the following provides a performance problem
Code: |
SELECT CASE COUNT(*)
WHEN 0 THEN 'N'
ELSE 'Y
END AS RESPONSE
INTO :HOST-VARIABLE
FROM your-table
WHERE EMPLOYEE_NO = :HOST-VARIABLE
|
|
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Is it important that the SQL should return these values?
Why not
Code: |
EVALUATE SQLCODE
WHEN 0
MOVE 'Y' TO variable
WHEN 100
MOVE 'N' TO variable
|
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
seems the laughs are on me.
the count(*) will force an index scan.
so SELECT 'Y' INTO .. when..
IF SQLCODE <> 0 then move 'N' end-if |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
actually the count forces a groupby.
the idxscan is already there. |
|
Back to top |
|
|
|