aleyva
asked on
Using DECODE in a where clause
Using DECODE in where clause
I have a where clause that I want to be based on a parameter value. I could have different queries but I thought that I would try DECODE. The other challange is that the decode is in an IN clause. Here is the code.
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and CT.trip_grade in
(decode ('&GradeName','PreK','1',
'K','(1,2)',
'1','(''1'',''2'')',
'2','1,2',
'3','1,2,3',
'4','2,3',
'5','2,3',
'6','2,3,4',
'7','3,4',
'8','3,4',
'9','3,4',
'10','4',
'11','4',
'12','4','12345'));
This works fine if I enter PreK, 10, 11 or 12, but any other doesn't work. How can I introduce the list of options in the IN clause usint the DECODE THEN portion if I have more than one to return?
I tried two other approaches with 'K', and '1', but they don't work.
Angel
I have a where clause that I want to be based on a parameter value. I could have different queries but I thought that I would try DECODE. The other challange is that the decode is in an IN clause. Here is the code.
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and CT.trip_grade in
(decode ('&GradeName','PreK','1',
'K','(1,2)',
'1','(''1'',''2'')',
'2','1,2',
'3','1,2,3',
'4','2,3',
'5','2,3',
'6','2,3,4',
'7','3,4',
'8','3,4',
'9','3,4',
'10','4',
'11','4',
'12','4','12345'));
This works fine if I enter PreK, 10, 11 or 12, but any other doesn't work. How can I introduce the list of options in the IN clause usint the DECODE THEN portion if I have more than one to return?
I tried two other approaches with 'K', and '1', but they don't work.
Angel
or
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and substr(decode ('&GradeName','PreK','1',
'K','1,2',
'1','1,2',
'2','1,2',
'3','1,2,3',
'4','2,3',
'5','2,3',
'6','2,3,4',
'7','3,4',
'8','3,4',
'9','3,4',
'10','4',
'11','4',
'12','4','12345'),CT.trip_ grade,1)>= 1;
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and substr(decode ('&GradeName','PreK','1',
'K','1,2',
'1','1,2',
'2','1,2',
'3','1,2,3',
'4','2,3',
'5','2,3',
'6','2,3,4',
'7','3,4',
'8','3,4',
'9','3,4',
'10','4',
'11','4',
'12','4','12345'),CT.trip_
ASKER
What about something like "colname in ('1','2','3')"
How can I get DECODE to return that type of result?
Would the DECODE be faster or the OR?
A
How can I get DECODE to return that type of result?
Would the DECODE be faster or the OR?
A
If trip_grade is a varchar2 then:
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and (&GradeName = 1 and CT.trip_grade in ('1','2')
or &GradeName = 2 and CT.trip_grade in ('1','2')
or ... etc. etc.
)
In your original post, you sometimes had (1,2) and sometimes had ('1','2'), which I found odd. Column trip_grade is either a NUMBER or a VARCHAR2, it can't be both.
As for whether the DECODE or OR is faster (assuming you could get the DECODE to work), I don't suppose it makes any difference: both will cause a full table scan.
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and (&GradeName = 1 and CT.trip_grade in ('1','2')
or &GradeName = 2 and CT.trip_grade in ('1','2')
or ... etc. etc.
)
In your original post, you sometimes had (1,2) and sometimes had ('1','2'), which I found odd. Column trip_grade is either a NUMBER or a VARCHAR2, it can't be both.
As for whether the DECODE or OR is faster (assuming you could get the DECODE to work), I don't suppose it makes any difference: both will cause a full table scan.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, the reason for the odd coding in my example is because I tried those three different approaches, sorry I wasn't clear on that.
What I am going to consider is the table mapping solution. This will make the maintance much easier because if the mapping changes, then all I have to do is change the data in the database, and not have to change the Forms code.
Let's see how this works out for me.
A
What I am going to consider is the table mapping solution. This will make the maintance much easier because if the mapping changes, then all I have to do is change the data in the database, and not have to change the Forms code.
Let's see how this works out for me.
A
You could rewrite like this:
select CT.comp_triplet_id, CT.ach_comp_triplet
from compendix_triplet CT
where CT.subsubject_name = '&PrimarySubject'
and (&GradeName = 1 and CT.trip_grade in (1,2)
or &GradeName = 2 and CT.trip_grade in (1,2)
or ... etc. etc.
)