|
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: 6965
Location: porcelain throne
|
|
|
|
| Quote: |
CASE statement is prohibited, as it creates performance issues.
|
    |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6965
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: 6965
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: 6965
Location: porcelain throne
|
|
|
|
actually the count forces a groupby.
the idxscan is already there. |
|
| Back to top |
|
|
|
|
