asked on

Oracle - Query carrier status issue

Hello all

I have this query that i pull the carrier mode.

But what i would like to do it so refer to the decoded carrier_mode column for each carrier ID and tell me what is the value in that specific column.

Ex: In ID field, i could have PROWEST_32.
In CARRIER_MOD decoded value i have T for TL.

with this info, i would look for the value for Carrier ID PROWEST_32 in column TL and see what is the value.

It could be Y or N (Yes or No)

I don't want to have all column just the good value for the real carrier type

How can i do that?

Thanks again for your help.

 SELECT 
     ID, 
     LTL,
     TL,
     INTERMODAL,
     RAIL,
     DECODE(CARRIER_MODE , 'T', 'TL', 'L', 'LTL', 'I', 'INTERMODAL', 'R', 'RAIL') as CARRIER_TYPE
     
FROM 
     CARRIER;

Open in new window

carrier.jpg

SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Actually, i was more looking at a result like the picture bellow where in the STATUS column (New column created to put the value), i would have the value from the CARRIER_MODE name column based on the picture from my previous post.

But to validate the CARRIER_MODE column fo fit with the other column name, i need to decode the CARRIER_MODE.

slightwv (䄆 Netminder)

I'm not understanding. Are you looking to create a 'status' based on the carrier_mode values?

Something like:

select id, case when carrier_mode in ('T','L','I','R') then 'N' else 'Y' end status,
DECODE(CARRIER_MODE , 'T', 'TL', 'L', 'LTL', 'I', 'INTERMODAL', 'R', 'RAIL') as CARRIER_TYPE
FROM CARRIER;

If not, please post some raw sample data and expected results. And possible explain more about what the status column does and why it does what it does.

Wilder1626

ASKER

I think this way it will be easier to understand.

So on the left, you have all column from the table.
In column F you have the decoded value:

DECODE(CARRIER_MODE , 'T', 'TL', 'L', 'LTL', 'I', 'INTERMODAL', 'R', 'RAIL') as CARRIER_TYPE

Open in new window

But i also have a column named the same as the decode carrier type value.

In yellow, are the Y (Yes) value while the others are N (No), attached to the ID and carrier mode.

On the right, will be the result where you have the ID column, also the CARRIER_TYPE column decoded.

But instead of 4 columns (LTL, TL, INTERMODAL and RAIL,), now i only have 1 column called STATUS, but i have the value from the real column names.
Table-extract1.xls

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

That look pretty good.

Small question, is it hard to replace the Y and N to be YES or NO in the result in column STATUS?

slightwv (䄆 Netminder)

>>is it hard to replace the Y and N to be YES or NO in the result in column STATUS?

Not at all.

Just one more case or decode statement. I'll use decode since you are already familiar with it:

...
decode(DECODE(CARRIER_MODE , 'T', TL, 'L', LTL, 'I', INTERMODAL, 'R', RAIL),'Y','YES','N','NO') as STATUS,
...

Wilder1626

ASKER

Thank you so much for your help again.

I will try to convert that using the CASE instead of DECODE.

For now, here is my final result:
SELECT ID,
VENDOR_NBR,
CORP_NAME,
CORP_ADDR1,
CORP_CITY,
CORP_STATE,
CORP_ZIP,
COUNTRY,
DECODE (DECODE (CARRIER_MODE,
'T',
TL,
'L',
LTL,
'I',
INTERMODAL,
'R',
RAIL),
'Y',
'YES',
'N',
'NO')
AS ACTIVE_IN_TMS,
DECODE (CARRIER_MODE,
'T',
'TL',
'L',
'LTL',
'I',
'INTERMODAL',
'R',
'RAIL')
AS CARRIER_TYPE,
DECODE (FP_VENDOR_INFO.VENDOR_STATUS_CODE,
'A',
'ACTIVE',
'C',
'CREDIT HOLD',
'H',
'HOLD',
'I',
'INACTIVE')
AS STATUS_CODE
FROM CARRIER, TMMGR.FP_VENDOR_INFO
WHERE CARRIER.ID = FP_VENDOR_INFO.CARRIER_ID
ORDER BY ACTIVE_IN_TMS DESC, ID;

This is perfect.