IBM Mainframe Forum

by **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.

by **Akatsukami** » Fri Mar 16, 2012 1:43 am

Let there be tables

Select all

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 all

SELECT A.VBLURB, B.VBRAND FROM
VEHTYPE A, VEHICLE B
WHERE A.VNO = B.VNO

gives

Select all

VBLURB VBRAND
------ --------------------
MC Yamaha
MC Harley Davison
CAR Honda
CAR BMW
MC Suzuki
CAR Audi
BUS Volvo
BUS Tata
MC Kawasaki

by **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..

by **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;

by **GuyC** » Fri Mar 16, 2012 3:37 pm

substr('xxMC CARBUS',VehicleNo*3,3)

IBM Mainframe Forum

Select query help

Select query help

Re: Select query help

Re: Select query help

Re: Select query help

Re: Select query help