Select query help



IBM's flagship relational database management system

Select query help

Postby mathew28 » Fri Mar 16, 2012 1:13 am

Hi,

My table has the below data

VehicleNo VehicleName
01 Yamaha
01 Harley Davison
02 Honda
02 BMW
01 Suzuki
02 Audi
03 Volvo
03 Tata
01 Kawasaki


When I give a select query, I want to display the vehicle type instead of the numbers like shown below . is it possible ? is there any option available ? Can Replace function be used ?
How ?

Select VehicleNo, VehicleName from Vehicle ;

VehicleNo VehicleName
MC Yamaha
MC Harley Davison
CAR Honda
CAR BMW
MC Suzuki
CAR Audi
BUS Volvo
BUS Tata
MC Kawasaki

01 Should be replaced by MC, 02 should be replaced by CAR, 03 should be replaced by BUS.
Note: The table should not be updated, Only on the select query output it should display in the shown format.

if possible, please help.
mathew28
 
Posts: 30
Joined: Tue Oct 20, 2009 11:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: Select query help

 

Re: Select query help

Postby Akatsukami » Fri Mar 16, 2012 1:43 am

Let there be tables
CREATE TABLE VEHICLE                     
(VNO             SMALLINT        NOT NULL,
 VBRAND          CHAR (20)       NOT NULL)
   IN FOO.BAR;                           
                                         
CREATE TABLE VEHTYPE                     
(VNO             SMALLINT        NOT NULL,
 VBLURB          CHAR (3)        NOT NULL)
   IN FOO.ABBAS;                         

containing the desired data. Then the query
SELECT A.VBLURB, B.VBRAND FROM
  VEHTYPE A, VEHICLE B       
  WHERE A.VNO = B.VNO         

gives
VBLURB  VBRAND             
------  --------------------
MC      Yamaha             
MC      Harley Davison     
CAR     Honda               
CAR     BMW                 
MC      Suzuki             
CAR     Audi               
BUS     Volvo               
BUS     Tata               
MC      Kawasaki           
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Select query help

Postby mathew28 » Fri Mar 16, 2012 1:57 am

Thanks for the suggestion. But, my issue is different.

sorry for not properly explaining my requirement.

I require this to happen for a report generation, so i should not create new column or new table. (That is why i mentioned no update allowed)
Also I'm not using cobol, so that i can check the value and display the respective vehicle types..

I'm executing a SQL query with multiple joins in a JCL and extracting that report to a dataset.

As, mentioned in my original post, the vehicle number is the field that exists and the vehicle type does not exist in any of the tables.

So, I wanted to know if there is any option to change the values dynamically when executing the select query.. Is there any built in functions available like replace or aliasname..something like that..
mathew28
 
Posts: 30
Joined: Tue Oct 20, 2009 11:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: Select query help

Postby mathew28 » Fri Mar 16, 2012 2:21 am

Hi

Thanks for stopping by.. I got the solution. it is

SELECT CASE VehicleNo WHEN '01' THEN 'MC'
WHEN '02' THEN 'CAR'
WHEN '03' THEN 'BUS'
ELSE NULL
END AS "TYPE"
FROM VEHICLE;
mathew28
 
Posts: 30
Joined: Tue Oct 20, 2009 11:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: Select query help

Postby GuyC » Fri Mar 16, 2012 3:37 pm

substr('xxMC CARBUS',VehicleNo*3,3)
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post