formatting my table...

[arravalli12]

I have a table.
my original query was:
SELECT CDE_NO, STR_NO, AVL_BAL
FROM table
WHERE STR_NO in (2250,2211) order by cde_no

code# , store#, avl_bal.
86 2250 5
86 2210 2
87 2250 5
88 2250 1
88 2210 5

Now I have to compare my inventories on stores 2250 and 2210
and display in the following format;

code# store2250 Bal store2210 bal
------------------------------------
86 5 2
87 5
88 1 5

I tried but could not get that format. I will appreciate if can get quick help.
Thanks

 

[PHV]

Something like this ?
SELECT A.CDE_NO, B.AVL_BAL store2250 Bal, C.AVL_BAL store2210
FROM table A
LEFT JOIN table B ON A.CDE_NO = B.CDE_NO AND B.STR_NO = 2250
LEFT JOIN table C ON A.CDE_NO = C.CDE_NO AND C.STR_NO = 2210
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arravalli12]

Thanks for the reply. The problem I have now is am querying on IBM mainframe and it does not like alias name and have difficulty pulling the correct record..
RS(avg_bal) gives same for both stores.
RS(a.avg_bal) gives error
RS("2210") also gives error.

I am just stuck up here.
Any help again...

 

[PHV]

You asked in the ANSI SQL Forum so you got an ANSI SQL reply.
Have you tried to play with the AS keyword ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244