need help in this query
-
select terms.key,
CASE plan.desc
when plan.desc like 'Lendered%' then terms.amt as "Broked"
when plan.desc then terms.amt
end as plandesc
from terms, plan
where terms.key = plan.key and desc like 'Lendered%'
I am trying to use cases in the statement to get the data and amount is equl to broked when desc is equal to lendered or regular amoout if desc is different then lendered
Thanks
-
you did not denote what the regular amount field is.
YOu cannot use the As "Broked" within the case statement
select terms.key,
CASE
when plan.desc like 'Lendered%' then terms.amt
else RegularAmount
end as plandesc
from terms, plan
where terms.key = plan.key and desc like 'Lendered%'
-
Try this:
select terms.key,
CASE
when plan.desc like 'Lendered%' then terms.amt as "Broked"
when plan.desc then terms.amt
end as plandesc
from terms, plan
where terms.key = plan.key and desc like 'Lendered%'
rather than:
select terms.key,
CASE plan.desc
when plan.desc like 'Lendered%' then terms.amt as "Broked"
when plan.desc then terms.amt
end as plandesc
from terms, plan
where terms.key = plan.key and desc like 'Lendered%'
A.J.
DBA with an attitude -
WIthout knowing if you have got a different fields for the brokered and regular amounts, you would need a case statement for each field to be outputed.
The following script should get you on the right track:
select terms.key,
CASE
when plan.desc like 'Lendered%'
then 'Brokered'
else 'Regular'
end as PlanDesc ,
case
when plan.desc like 'Lendered%'
then terms.amt/* the lendered amount */
else terms.amt /* the regular amount, change field name is necessary */
end as Amount
from terms, plan
where terms.key = plan.key
Robert
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply