?
Solved

oracle query

Posted on 2013-05-17
9
Medium Priority
?
299 Views
Last Modified: 2013-05-17
select distinct product_formula,inv_product_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_PRODUCT_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.
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39174643
We've helped you with enough CASE statements over the last few questions.  What about them is still confusing to you?

Always start with a simple test to verify the logic then expand it.

Here's the simple test:
select
    case
        when 1=2 then 'a'
        when 1=3 then 'b'
        when 1=1 then 'c'
   end
from dual
/


This is untested but it should go something like this:
Case
when inv_product_type = 'RPFF' and substr(product_formula,4,2) = 'A2' then 'RPFF Recovered'
when inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A2' then 'RPL Recovered'
when inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A5' then 'RPL Allogeneic Salvage'
when inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A3' then 'RPL Autologous Salvage'
end
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39174653
IS THIS WHAT YOU ARE AFTER? http://sqlfiddle.com/#!4/f167b/4 
select distinct
  product_formula
, inv_product_type
, CASE WHEN inv_product_type = 'RPFF' and substr(product_formula,4,2) = 'A2' then 'RPFF Recovered' 
       WHEN inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A2' then 'RPL Recovered' 
       WHEN inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A5' then 'RPL Allogeneic Salvage' 
       WHEN inv_product_type = 'RPL' and substr(product_formula,4,2) = 'A3' then 'RPL Autologous Salvage'
  ELSE NULL
  END AS X
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')

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39174658
oops caps on - sorry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174677
>>oops caps on - sorry

You can edit your posts to make corrections until someone else posts...
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39174687
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39174701
>>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.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39174714
thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174718
>> as they did as a young man on a 3270 terminal

I too worked on one of those back in the day...
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39174792
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}
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question