*There are three statuses 0=Initiated, 1=In Progress, 2=Completed
I need a query to get the result rows as per below:
-----------------------------------------------------------------------
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |
* If Status=2 then completed else it is not completed.
Please see attached image to view tables.
Please help me, how can I get result rows.
Thank you.
I changed the tables so that I can't share my company's data.
Also Here is what I have tried in MySQL,
SELECT veh.VEHICLE_CLASS_ID, vehclass.VEHICLE_CLASS_NAME,
IF(STATUS=2,GROUP_CONCAT(`VEHICLE_ID`),'') COMPLETED_ VEHICLES,
IF(STATUS<2,GROUP_CONCAT(`VEHICLE_ID`),'') NOT_COMPLETED_ VEHICLES
FROM VEHICLE veh LEFT JOIN VEHICLE_CLASS vehclass
on veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID GROUP BY veh.VEHICLE_CLASS_ID having veh.VEHICLE_CLASS_ID is not null;
But its not working.
I need the solution in four different databases, i.e. Oracle, MySQL, MSSQL and IBM DB2.
I know it has to do something with 'DECODE' function in Oracle but as a newbie, I am not proficient in writing such complex queries.
You can give me more hint that will be OK. I am not asking for complete query.
SELECT vc.vehicle_class_name
, GROUP_CONCAT(CASE WHEN v.status = 2 THEN v.vehicle_id END) completed
, GROUP_CONCAT(CASE WHEN v.status <> 2 THEN v.vehicle_id END) incomplete
FROM vehicle_class vc
JOIN vehicle v
ON v.vehicle_class_id = vc.vehicle_class_id
GROUP
BY vc.vehicle_class_id;
I need help converting in Oracle9i, DB2 and MSSql.
*There are three statuses 0=Initiated, 1=In Progress, 2=Completed
I need a query to get the result rows as per below:
-----------------------------------------------------------------------
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |
* If Status=2 then completed else it is not completed.
Please see attached image to view tables.
Please help me, how can I get result rows.
Thank you.
Two notes.
1- Result set is not clear; only vehicle_001 is of class ABC but three vehicles are listed in the report ?!
2- Do not think DECODE() will cut it for you, specially if you need to write a query that is compatible with four different RDBMS engines.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Reply With Quote
This is not the homework
Originally Posted by deduyay
