Solved

Oracle - Query carrier status issue

Posted on 2013-01-24
8
337 Views
Last Modified: 2013-01-24
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 tablecarrier.jpg
0
Comment
Question by:Wilder1626
[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
  • 4
  • 3
8 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 25 total points
ID: 38816570
I'm not sure what you exactly want , but lets give it a try

SELECT
     ID,
     TL
FROM
     CARRIER
where id = 'PROWEST_32' and
DECODE(CARRIER_MODE , 'T', 'TL', 'L', 'LTL', 'I', 'INTERMODAL', 'R', 'RAIL') = 'TL';
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38816669
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.


carrier 2
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38816907
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 11

Author Comment

by:Wilder1626
ID: 38816958
I think this way it will be easier to understand.

picture 3
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
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 475 total points
ID: 38817020
Thanks for the update.

See if this works for you .  I prefer CASE over DECODE.  CASE is more standard and DECODE is only Oracle.

I provided both (I left your original decode).

SELECT ID, 
	case carrier_mode
		when 'T' then TL
		when 'L' then LTL
		when 'I' then INTERMODAL
		when 'R' then RAIL
	end status,
	DECODE(CARRIER_MODE , 'T', 'TL', 'L', 'LTL', 'I', 'INTERMODAL', 'R', 'RAIL') as CARRIER_TYPE
FROM carrier;

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

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38817028
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?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38817048
>>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,
...
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38817119
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.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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