SQL & PL/SQL

I have created a table called ct_temp and it's structure is:



create table ct_temp(id char(2),srs number(3),amt number);

Result : Table Created

I now insert the following rows into the table:

id srs amt

1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10

Commit

Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)

criteria : I then want to retrieve the following:

for srs = 62, all values that is greater than 10,000


Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000

Works like a charm and retrives the results.

Now comes the big issue


I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.

Typically I wrote the query as:

select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)


Expected results should be:


srs amt

62 30000

62 16000

65 10


I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".

I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.

I have tried the following in the office:

using EXISTS operator = no luck
using INLINE VIEW = no luck
using UNION operator = No luck

Is there any way around? Please help me guys.

Sandeep

NOTE: The reason why I have used DECODE function is because in Production environment there are columns called

a) return line item which has numerous values like '001', '002', '062', '067'

b) there is another column called amount

c) so for every return line item there is an amount

so the business wants:

d) if the line item number = 62 then the amount should be dispplayed and it should be displayed as "AB Taxable income"

e) if the linte item number = 65 then the amount should be retrived and displayed as "Amount taxable in AB"


So seeing these multiple conditions for a "SELECT" statement, I used DECODE fuction.