How solve this case condition and if condition in oracle 11g

Posted by Cpatil1000 under Oracle on 9/19/2017 | Points: 10 | Views : 244 | Status : [Member] | Replies : 0
Hi,
I am new in oracle. Our organization has shifted database from SQL server to oracle server. So i am getting this error in case condition and if condition..

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;




Responses

(No response found.)

Login to post response