Joined: 11 Oct 2008 Posts: 74 Location: Chicago, IL
Hello,
I am running the following query
Select *
From Tab1
Where
Col1="XYZ" And
(Col2 like :ws-find or
Col3 like :ws-find or
Col4 like :ws-find or
Col5 like :ws-find or
Col6 like :ws-find or
Col7 like :ws-find)
I need to find the column name from which condition it is picking the data..
That is like which column is having ws-find.
COL2/COL3/COL4/COL5/COL6/COL7.
Also the length of ws-find is not fixed.
Its PIC X(10) but data may be of 3/4/5... character.
I am thinking of something like this (not tested, just an idea):
Code:
SELECT 'COL2',COL1,COL2,.. COLn
FROM TAB1
WHERE COL1 = 'XYZ' AND COL2 LIKE :WS-FIND
UNION
SELECT 'COL3',COL1,COL2,.. COLn
FROM TAB1
WHERE COL1 = 'XYZ' AND COL3 LIKE :WS-FIND
.
.
UNION
SELECT 'COL7',COL1,COL2,.. COLn
FROM TAB1
WHERE COL1 = 'XYZ' AND COL7 LIKE :WS-FIND
You can then test on the first column which query returned your row, if any. You might get more than one row back if more than one column satisfied the query.
I think a solution with some logic in the column list could do the trick, but I will let you do your own lookup for that in the SQL manual, and then come back if you have questions.
select
col1, case when col1 like ? then 'TRUE' else 'FALSE' end as Col1_cond,
col2, case when col2 like ? then 'TRUE' else 'FALSE' end as Col2_cond,
col3, case when col3 like ? then 'TRUE' else 'FALSE' end as Col3_cond,
col4, case when col4 like ? then 'TRUE' else 'FALSE' end as Col4_cond,
from table1
where ...
would avoid the possible n index-/tablescans of previous solution
and only returns each row only once.
