I have a below query,i tried it but not able to come up with the exact solution.
I have two tables.
Table 1 has a column ID and table 2 have column CODE
NOW for every ID in table 1 there can be multiple rows in table 2 with different values of CODE.

I have to write a query such that for every ID in table 1 search (run through) table 2 to select a code which is valid( it will be valid if it has any of the value A,B,C,D) IF it is not valid make the code as X.

I think you can do something as below. The first 2 columns should give you what you want and then you may get rid of the last column if needed to. This is Not tested as I don't have the access to mainframe as of now.

Code:

SELECT
A.ID,
CASE B.CODE
WHEN 'A' THEN 'A'
WHEN 'B' THEN 'B'
WHEN 'C' THEN 'C'
WHEN 'D' THEN 'D'
ELSE 'X'
END,
CASE B.CODE
WHEN 'A' THEN '3A'
WHEN 'B' THEN '2B'
WHEN 'C' THEN '4C'
WHEN 'D' THEN '5D'
ELSE '1X'
END AS MY_ORDER
FROM TABLE1 A, TABLE2 B
ORDER BY MY_ORDER;

RahulG31, I think, you are missing the relation between two tables.
Arun, I would like that approach but what if table2 doesn't have a matching row? in your case it will still be marked as 'X' but OP wants to mark X only for the entries other than (ABCD).

Nitin,
Welcome!
Before you expect a solution to the problem, please state the problem correctly along with the table structure, sample data and the desired output because right now it is a guess work.

However, based on what is stated, try this.

Code:

select T3.ID1,
T3.code2
from
(SELECT
T1.ID as ID1,
T2.code1 as code2,
case when T2.code1 = 'X' then 1
when T2.code1 = 'B' then 2
when T2.code1 = 'A' then 3
when T2.code1 = 'C' then 4
when T2.code1 = 'D' then 5
end
FROM TABLE1 T1,
(SELECT ID,case when CODE NOT IN ('A','B','C','D') then 'X' else CODE end as code1
FROM TABLE2 ) T2
where T1.ID = T2.ID
order by 3) as T3

SELECT ID, MIN(CODE)
FROM
(
SELECT
A.ID as ID,
CASE B.CODE
WHEN 'A' THEN 'A'
WHEN 'B' THEN 'B'
WHEN 'C' THEN 'C'
WHEN 'D' THEN 'D'
ELSE 'X'
END as CODE,
CASE B.CODE
WHEN 'A' THEN '3A'
WHEN 'B' THEN '2B'
WHEN 'C' THEN '4C'
WHEN 'D' THEN '5D'
ELSE '1X'
END AS MY_ORDER
FROM TABLE1 A, TABLE2 B
WHERE A.MATCHING_COLUMN = B.MATCHING_COLUMN
ORDER BY 1,3,2
)
GROUP BY ID;

i came up the sql what arun wote , that will not work as it will give multiple rows

But in your recent post you mentioned,

Quote:

if valid value of code is there same value should be repeated for that ID

Can you show what have you tried and what are you getting out of it? Based on the new details, you might need to change it to JOIN on both ID and MERCHANT. However the question on whether ID, MERCHANT keys from table1 will always be available in table2, and if not what would be the expected output still remains.