Query from Sqlserver to Crystal Report

[TariqMehmod]

Sir I have this query in sqlserver query editor

SELECT man1, gate, vehicle,weight/(IIF(LEN(man1)=0, 0, 1)+IIF(LEN(man2)=0, 0, 1)
+IIF(LEN(man3)=0, 0, 1)+IIF(LEN(man4)=0, 0, 1))as wages
  FROM Table_1 
  WHERE  LEN(man1)>0 
UNION ALL
SELECT man2, gate, vehicle,weight/(IIF(LEN(man1)=0, 0, 1)+IIF(LEN(man2)=0, 0, 1)
+IIF(LEN(man3)=0, 0, 1)+IIF(LEN(man4)=0, 0, 1))as wages
  FROM table_1 
  WHERE LEN(man2) >0 
UNION ALL
SELECT man3, gate, vehicle,weight/(IIF(LEN(man1)=0, 0, 1)+IIF(LEN(man2)=0, 0, 1)
+IIF(LEN(man3)=0, 0, 1)+IIF(LEN(man4)=0, 0, 1))as wages
  FROM table_1 
  WHERE LEN(man3) >0 
UNION ALL
SELECT man4, gate, vehicle,weight/(IIF(LEN(man1)=0, 0, 1)+IIF(LEN(man2)=0, 0, 1)
+IIF(LEN(man3)=0, 0, 1)+IIF(LEN(man4)=0, 0, 1))as wages
  FROM table_1 
  WHERE LEN(man4) >0 
ORDER BY 1 asc

The codes work fine as shown in image

Now I use same codes in Crystal report command like this

When I press OK then it show error message like this

Please help me to make my query workable in crystal report command box

I want to use this query in Crystal report

 SELECT odln.u_loader1, odln.DocNum, odln.u_truck,odln.U_WE2/(IIF(len(odln.u_loader1)=0, 0, 1)+IIF(len(odln.u_loader2)=0, 0, 1)+IIF(len(odln.u_loader3)=0, 0, 1)+IIF(len(odln.u_loader4)=0, 0, 1))
  FROM odln 
  WHERE  len(odln.u_loader1)>0 
UNION ALL
SELECT odln.u_loader2, odln.DocNum, odln.u_truck,odln.U_WE2/(IIF(len(odln.u_loader1)=0, 0, 1)+IIF(len(odln.u_loader2)=0, 0, 1)+IIF(len(odln.u_loader3)=0, 0, 1)+IIF(len(odln.u_loader4)=0, 0, 1))
  FROM odln 
  WHERE len(odln.u_loader2) >0 
UNION ALL
SELECT odln.u_loader3, odln.DocNum, odln.u_truck,odln.U_WE2/(IIF(len(odln.u_loader1)=0, 0, 1)+IIF(len(odln.u_loader2)=0, 0, 1)+IIF(len(odln.u_loader3)=0, 0, 1)+IIF(len(odln.u_loader4)=0, 0, 1))
  FROM odln 
  WHERE len(odln.u_loader3) >0 
UNION ALL
SELECT odln.u_loader4, odln.DocNum, odln.u_truck,odln.U_WE2/(IIF(len(odln.u_loader1)=0, 0, 1)+IIF(len(odln.u_loader2)=0, 0, 1)+IIF(len(odln.u_loader3)=0, 0, 1)+IIF(len(odln.u_loader4)=0, 0, 1))
  FROM odln 
  WHERE len(odln.u_loader4) >0 
ORDER BY 1 asc

Please help

 

[kray4660]

By the way according to how your code is written. You could (rather unlikely) get a divide by zero situation. Also IIF was introduced with SQL Server 2012. If somehow you are connecting to an older database then you could get an error. You did not state how you were connecting to the database in Crystal. You could use the CASE WHEN statement as an alternative. CASE WHEN should be good for all versions of SQL