How improve this oracle case condition and if condition into oracle
hi,I have written this sql query in oracle. Because of our organization has shifted sql server to Oracle server.
create or replace Procedure SELECT_PROJECT_TOLL_SUMMARY
(
P_PROJECT_ID VEHICLE_MASTER.PROJECT_ID%TYPE,
P_TOLL_ID VEHICLE_MASTER.TOLL_ID%TYPE,
P_FROMDATE_TO VEHICLE_MASTER.DATE_TO%TYPE
)
As
Begin
Select Distinct P.SECTION_ID, P.PROJECT_NAME, T.TOLL_REGIMENT_NAME,
Count(V.VEHICLE_ID) As "TOTAL_TRAFFIC",
"FROMDATE" = (Case When P.SECTION_ID = 2 Then To_Char(MAX(V.DATE_TO), 'dd-MM-yyyy') + " " + "07:30:00 AM"
Else To_Char(MAX(V.DATE_TO), 'dd-MM-yyyy') + " " + "08:00:00 AM" End) ,
"TODATE" =(Case When P.SECTION_ID = 2 Then To_Char(P_FROMDATE + 1) + " " + "07:59:59 AM"
Else To_Char(SYSDATE + "dd-MM-yyyy") + " " + "07:59:59 AM" End) ,
'LMV' = Count(Case When V.CLASS_ID In (1, 10) And VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End),
'LCV' = Count(Case When V.CLASS_ID In (2, 11) And VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End),
'HVM' = Count(Case When V.CLASS_ID In (5, 6, 9) And VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End),
'HCM' = Count(Case When V.CLASS_ID In (3) And VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End),
'MULTIAXEL' = Count(Case When V.CLASS_ID In (4, 7, 8, 12) And VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End),
'TOTAL_EXEMPTED' = To_Char(int, IsNull(Count(Case When VTM.TYPE Like 'Exempted%' Then V.VEHICLE_ID End),0)),
'TOTAL_NON_EXEMPTED' = To_Char(int,Count(Case When VTM.TYPE NOT Like 'Exempted%' Then V.VEHICLE_ID End))
From VEHICLE_MASTER V
Inner Join TOLL_MASTER T On V.TOLL_ID = T.TOLL_ID
Inner Join PROJECT_MASTER P On T.PROJECT_ID = P.ID
Inner Join VEHICLE_TYPE_MASTER VTM On VTM.ID = V.TYPE_ID
Inner Join VEHICLE_TICKET_DETAIL VT On V.VEHICLE_ID = VT.TICKET_ID And V.TOLL_ID = VT.TOLL_ID
Group By P.SECTION_ID, P.ID, P.PROJECT_NAME, T.TOLL_REGIMENT_NAME
Order by p.SECTION_ID desc;
End SELECT_PROJECT_TOLL_SUMMARY;