×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query from Sqlserver to Crystal Report

thread149-1801537

Query from Sqlserver to Crystal Report

Query from Sqlserver to Crystal Report

(OP)
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

RE: Query from Sqlserver to Crystal Report

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close