Oracle Query Help

Hello,
I have the below query
SELECT dpgm.PROGRAM_CODE as P1,
    	dpgm.PROGRAM_CODE, 
	
     fid.PO_NUMBER as VENDOR_PO_NUMBER,
     case
    when FID.CONSIGNMENT_TYPE LIKE 'SAIC%' THEN 'SAIC OWNED'
    WHEN FID.CONSIGNMENT_TYPE LIKE 'Consignment%' THEN 'Consignment'
    when fid.CONSIGNMENT_TYPE is null then 'Review'
    end as  CONSIGNMENTTYPE
from  INVOICE fid

Open in new window

Please help me write the modified query to include a condition that when the consignmenttype is null look for the field fid.PO_NUMBER and if it begins with '9' then mark it as consignment and if it begins with '4' then mark it as Saic owned else mark it as review
Thanks all
Star79Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Please provide sample data and expected results.

I think a simple NVL with a simple CASE statement will work but I'm not completely understanding what you want.

Guessing something like:
NVL(consignmenttype , case substr( fid.PO_NUMBER ,1,1) when '4' then 'GOT A 4' when '9' then 'GOT A 9' end)

I'm just not sure what you want to use in place of the 'GOT A' strings.
Star79Author Commented:
Vendor PO      Consignment
45106876      Saic Owned
                     Review
95103996      Consignment

 Please see above the sample result set.This is the case for only when the consignment_type is null.
But the first 2 conditions on the case statement remains the same.
slightwv (䄆 Netminder) Commented:
Is this what you are after?

select
...
case
    when FID.CONSIGNMENT_TYPE LIKE 'SAIC%'   or (consignmenttype is null and substr( fid.PO_NUMBER ,1,1) = '4' ) THEN 'SAIC OWNED'
    WHEN FID.CONSIGNMENT_TYPE LIKE 'Consignment%' or (consignmenttype is null and substr( fid.PO_NUMBER ,1,1) = '9' ) THEN 'Consignment'
    when fid.CONSIGNMENT_TYPE is null then 'Review'
    end as  CONSIGNMENTTYPE
...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Star79Author Commented:
the above did not work.Its putting review on all the consignment_type that has a null.Its not flagging them based on vendor_po beginning with '4' or '9'
slightwv (䄆 Netminder) Commented:
I cannot reproduce what you describe.

Here is a quick test case I set up and the results:
drop table tab1 purge;
create table tab1(consignment_type varchar2(15), po_number varchar2(10));

insert into tab1 values('SAIC','12345');
insert into tab1 values('SAIC','666666');
insert into tab1 values(null,'42345');
insert into tab1 values(null,'92345');
insert into tab1 values('Consignment','12345');
insert into tab1 values('Consignment','66666');
insert into tab1 values('Bob','66666');
insert into tab1 values(null,'66666');
commit


select consignment_type, po_number, 
case
     when CONSIGNMENT_TYPE LIKE 'SAIC%'   or (consignment_type is null and substr( PO_NUMBER ,1,1) = '4' ) THEN 'SAIC OWNED'
     WHEN CONSIGNMENT_TYPE LIKE 'Consignment%' or (consignment_type is null and substr( PO_NUMBER ,1,1) = '9' ) THEN 'Consignment'
     when CONSIGNMENT_TYPE is null then 'Review'
     end as  CONSIGNMENTTYPE
from tab1;

Open in new window


Results:
CONSIGNMENT_TYPE                              PO_NUMBER                      CONSIGNMENTTYPE
--------------------------------------------- ------------------------------ ------------------
SAIC                                          12345                          SAIC OWNED
SAIC                                          666666                         SAIC OWNED
                                              42345                          SAIC OWNED
                                              92345                          Consignment
Consignment                                   12345                          Consignment
Consignment                                   66666                          Consignment
Bob                                           66666
                                              66666                          Review

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Star79Author Commented:
Sorry just an update when po# is null we mark it as 'Review'
So for the thrid condition that if the fid.consignmentype is null we check to see if it beigns with 9 or 4 or null(then review)
slightwv (䄆 Netminder) Commented:
>>So for the thrid condition that if the fid.consignmentype is null we check to see if it beigns with 9 or 4 or null(then review)

You can never get there.  If consignmentype is null and po_number begins with a 4, you stated to set it to 'SAIC' it null and a 9, set it to 'Consignment'

So you will never get to the third case where it is null and starts with a 4 or 9.

Please add new values to the test case and provide updated expected results.
Star79Author Commented:
the third case is the check on null for po_number and not consignmenttype
Star79Author Commented:
If the consignment flag field can’t find a consignment or a SAIC owned attribute, then review the PO field…and if PO field number begins with a 4 then “SAIC Owned” or if PO field number begins with a 9 then “consignment”, if no PO #, then make “review”.
slightwv (䄆 Netminder) Commented:
>>the third case is the check on null for po_number and not consignmenttype

That doesn't make sense either.

If I replace consignmenttype  with po_number in what you posted I get:
So for the thrid condition that if the po_number is null we check to see if it beigns with 9 or 4 or null(then review)

How can it be null and begin with a 4 or 9?

>>If the consignment flag field can’t find a consignment...

You can keep explaining it all day long.  I'm not understanding.

I want sample data and expected results.  Please update my test case to add/change rows then post the results you want from that data.



I've posted the basic logic.  All you should need to do is move the logic around inside the case statement to whatever your requirements are.
PortletPaulEE Topic AdvisorCommented:
Star79 the best method for getting accurate results quickly when asking technical questions is to:

1. supply "sample data", and based only on that data
2. the "expected result"

It is your responsibility to provide the sample data, if we do this for you we could be making assumptions that are incorrect. Also, because you have the actual tables involved locating  few sample records generally isn't that difficult.

By following this method you clearly communicate your requirements without relying on purely on words. Plus the discipline of following this method helps you identify all requirements.
PortletPaulEE Topic AdvisorCommented:
Extending slightwv's sample data slightly for null po_number  rows
create table tab1(consignment_type varchar2(15), po_number varchar2(10));

insert into tab1 values('SAIC','12345');
insert into tab1 values('SAIC','666666');
insert into tab1 values('SAIC',null);
insert into tab1 values(null,'42345');
insert into tab1 values(null,'92345');
insert into tab1 values('Consignment','12345');
insert into tab1 values('Consignment','66666');
insert into tab1 values('Consignment',null);
insert into tab1 values('Bob','12345');
insert into tab1 values('Bob','445566');
insert into tab1 values('Bob','66666');
insert into tab1 values('Bob',null);
insert into tab1 values(null,'66666');

Open in new window

Using the following query
SELECT
      consignment_type
    , po_number
    , CASE
            WHEN CONSIGNMENT_TYPE LIKE 'SAIC%' 
              OR (consignment_type IS NULL AND substr(PO_NUMBER, 1, 1) = '4') THEN 'SAIC OWNED'
            WHEN CONSIGNMENT_TYPE LIKE 'Consignment%' 
              OR (consignment_type IS NULL AND substr(PO_NUMBER, 1, 1) = '9') THEN 'Consignment'
            WHEN CONSIGNMENT_TYPE IS NULL 
              OR PO_NUMBER IS NULL THEN 'Review'

            else 'not catered for in case expression'

      END AS CONSIGNMENTTYPE
FROM tab1
;

Open in new window

Produces this result:
| CONSIGNMENT_TYPE | PO_NUMBER |                    CONSIGNMENTTYPE |
|------------------|-----------|------------------------------------|
|             SAIC |     12345 |                         SAIC OWNED |
|             SAIC |    666666 |                         SAIC OWNED |
|             SAIC |    (null) |                         SAIC OWNED |
|           (null) |     42345 |                         SAIC OWNED |
|           (null) |     92345 |                        Consignment |
|      Consignment |     12345 |                        Consignment |
|      Consignment |     66666 |                        Consignment |
|      Consignment |    (null) |                        Consignment |
|              Bob |     12345 | not catered for in case expression |
|              Bob |    445566 | not catered for in case expression |
|              Bob |     66666 | not catered for in case expression |
|              Bob |    (null) |                             Review |
|           (null) |     66666 |                             Review |

Open in new window


NOTE
     With in the case expression, the WHEN conditions are evaluated in the order presented
Star79Author Commented:
i used the following:
 case
     when CONSIGNMENT_TYPE LIKE 'SAIC%'   or (consignment_type is null and substr( PO_NUMBER ,1,1) = '4' ) THEN 'SAIC OWNED'
     WHEN CONSIGNMENT_TYPE LIKE 'Consignment%' or (consignment_type is null and substr( PO_NUMBER ,1,1) = '9' ) THEN 'Consignment'
     when CONSIGNMENT_TYPE is null and po_number is null then 'Review'
     end as  CONSIGNMENTTYPE,
as part of my solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.