Page 1 of 1

Select query help

PostPosted: Fri Mar 16, 2012 1:13 am
by mathew28
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.

Re: Select query help

PostPosted: Fri Mar 16, 2012 1:43 am
by Akatsukami
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           

Re: Select query help

PostPosted: Fri Mar 16, 2012 1:57 am
by mathew28
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..

Re: Select query help

PostPosted: Fri Mar 16, 2012 2:21 am
by mathew28
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;

Re: Select query help

PostPosted: Fri Mar 16, 2012 3:37 pm
by GuyC
substr('xxMC CARBUS',VehicleNo*3,3)