anumoses
asked on
oracle query
select distinct product_formula,inv_produc t_type
from valid_products@plab.world
where inv_product_type in ('RPFF','RPL')
and product_formula is not null
and substr(product_formula,4,2 ) in ('A2','A3','A5')
PRODUCT_FORMULA|INV_PRODUC T_TYPE
@35A2 E3|RPL
@35A2 G7|RPFF
@35A2 G9|RPL
@35A3 G7|RPL
@35A3 ZZ|RPL
@35A5 E3|RPL
@35A5 G7|RPL
@35A5 ZZ|RPL
@36A2 G7|RPFF
@36A2 G9|RPL
@36A3 G7|RPL
@36A3 ZZ|RPL
@36A5 G7|RPL
@36A5 ZZ|RPL
@37A2 G7|RPFF
@37A2 G9|RPL
@37A3 G7|RPL
@37A3 ZZ|RPL
@37A5 G7|RPL
@37A5 ZZ|RPL
@61A3|RPL
@61A5|RPL
@62A3|RPL
@62A5|RPL
@63A3|RPL
@63A5|RPL
@BNA2 G7|RPFF
@BRA2 G7|RPFF
@BSA2 G7|RPFF
-------------------------- -
If inv_product_type = 'RPFF' and substr(product_formula,4,2 ) = 'A2' then
its 'RPFF Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A2' then
its 'RPL Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A5' then
its 'RPL Allogeneic Salvage'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A3' then
its 'RPL Autologous Salvage'
I need help in having a decode statement . If the description is chosen then the result has to be the substr value.
from valid_products@plab.world
where inv_product_type in ('RPFF','RPL')
and product_formula is not null
and substr(product_formula,4,2
PRODUCT_FORMULA|INV_PRODUC
@35A2 E3|RPL
@35A2 G7|RPFF
@35A2 G9|RPL
@35A3 G7|RPL
@35A3 ZZ|RPL
@35A5 E3|RPL
@35A5 G7|RPL
@35A5 ZZ|RPL
@36A2 G7|RPFF
@36A2 G9|RPL
@36A3 G7|RPL
@36A3 ZZ|RPL
@36A5 G7|RPL
@36A5 ZZ|RPL
@37A2 G7|RPFF
@37A2 G9|RPL
@37A3 G7|RPL
@37A3 ZZ|RPL
@37A5 G7|RPL
@37A5 ZZ|RPL
@61A3|RPL
@61A5|RPL
@62A3|RPL
@62A5|RPL
@63A3|RPL
@63A5|RPL
@BNA2 G7|RPFF
@BRA2 G7|RPFF
@BSA2 G7|RPFF
--------------------------
If inv_product_type = 'RPFF' and substr(product_formula,4,2
its 'RPFF Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Allogeneic Salvage'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Autologous Salvage'
I need help in having a decode statement . If the description is chosen then the result has to be the substr value.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops caps on - sorry
>>oops caps on - sorry
You can edit your posts to make corrections until someone else posts...
You can edit your posts to make corrections until someone else posts...
actually all I did was a tiny bit of editing to the words provided, 98% of the case expression was in the question
If inv_product_type = 'RPFF' and substr(product_formula,4,2 ) = 'A2' then
its 'RPFF Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A2' then
its 'RPL Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A5' then
its 'RPL Allogeneic Salvage'
If inv_product_type = 'RPL' and substr(product_formula,4,2 ) = 'A3' then
its 'RPL Autologous Salvage'
'its' to null
'if to WHEN
add case
add end
add an alias
remove some line breaks
done
& "decode hurts the eyes", think case expressions instead, much more maintainable
If inv_product_type = 'RPFF' and substr(product_formula,4,2
its 'RPFF Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Recovered'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Allogeneic Salvage'
If inv_product_type = 'RPL' and substr(product_formula,4,2
its 'RPL Autologous Salvage'
'its' to null
'if to WHEN
add case
add end
add an alias
remove some line breaks
done
& "decode hurts the eyes", think case expressions instead, much more maintainable
>>You can edit your posts to make corrections until someone else posts...
when challenged by your uncanny ability to answer before I've read the question, speed is of the essence, and I have been caught on several occasions (oracle topic in particular) where half-way through review I get stymied....
but yes, it was a possibility, but my brain and my hands don't always align as they did as a young man on a 3270 terminal - take pity.
when challenged by your uncanny ability to answer before I've read the question, speed is of the essence, and I have been caught on several occasions (oracle topic in particular) where half-way through review I get stymied....
but yes, it was a possibility, but my brain and my hands don't always align as they did as a young man on a 3270 terminal - take pity.
ASKER
thanks
>> as they did as a young man on a 3270 terminal
I too worked on one of those back in the day...
I too worked on one of those back in the day...
well mine was ICL teleprinter to beaureau to start, moving on through some small HP kit I can't even remember models of, a partly analog mainframe programmed by patch panel, quite a lot of HP1000 kit, through several 3270s (not all IBM backends) yada yada - & here I am with a SSD in a laptop and an iphone, where the phone alone is infinitely more powerful than the first few years of all that experience when added together.
sorry for the long response
{+ this was edited for spelling, another sorry needed}
sorry for the long response
{+ this was edited for spelling, another sorry needed}