Query from Sqlserver to Crystal Report

TariqMehmod · Mar 12, 2020

Sir I have this query in sqlserver query editor

Code:

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

Code:

 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 · Mar 12, 2020

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query from Sqlserver to Crystal Report

TariqMehmod

Programmer

kray4660

MIS

Similar threads

Part and Inventory Search

Sponsor