?
Solved

Crystal Reports Command SQL Query Syntax Error

Posted on 2007-10-11
15
Medium Priority
?
466 Views
Last Modified: 2012-08-14
Was placing the Sytax below in to the Crystal Reports Add Command window and got a failure. Can you identify what is wrong? We are running this off of a MS Access Database.

SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType01` AS `Account`, `ValueAmt01` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  (`ValueType01` <>0))

Please help
0
Comment
Question by:ruavol2
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 20059015
Does this work directly in Access?  This would be the firsth thing to test.
Second, you may want to create a second report, add the transaction table instead of a command, and then put some of the fields on the report.  You can then go to the Report menu, SQL Query to see the format that Crystal generated the SQL in.
From what I can see, you can create the report easily without even using a command.  Is there a reason you need to use a command?
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20059051
What are those characters wrapped around your column names and alisaes for?
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum, ValueType01 AS Account, ValueAmt01 AS Amount, UserID
FROM Transaction WHERE ( ( CardAmt <>0 ) OR ( CashAmt <> 0 ) OR ( CheckAmt <> 0 ) AND  (ValueType01 <>0))
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 800 total points
ID: 20059098
YOu may have to use the ALIAS in the from clause.  Also group the ORs in ( )

SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum, ValueType01 AS Account, ValueAmt01 AS Amount, UserID
FROM Transaction WHERE ( ( CardAmt <>0 ) OR ( CashAmt <> 0 ) OR ( CheckAmt <> 0 )) AND  (Account<>0)

mlmcc
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

Author Comment

by:ruavol2
ID: 20059245
I got "DAO too few parameters expected 1" as and error message when I put that in? Any ideas
0
 

Author Comment

by:ruavol2
ID: 20059272
That was directed at mlmcc?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 20059391
No ideas..

When do you get the error?  When you add the command or when you preview the report?

mlmcc
0
 

Author Comment

by:ruavol2
ID: 20059419
When I Click OK on the Add Command
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 20059500
If you take the where clause off do you get the error/

mlmcc
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 20059510
Why are you using a command?  You can do this by adding the table to a report, placing the desired fields on the report, and defining the record selection.
If you do it that way, you won't need to deal with the syntax and it is easier to maintain in the future.
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 400 total points
ID: 20059577
I don't think the sql is the issue here (anymore).  Using the same connection can you add tables to the report successfully?
0
 

Author Comment

by:ruavol2
ID: 20059580
bbreed35 I am actually have a series of commands like:

SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType01` AS `Account`, `ValueAmt01` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType01` <>0)
UNION ALL
SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType02` AS `Account`, `ValueAmt02` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType02` <>0)
UNION ALL
SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType03` AS `Account`, `ValueAmt03` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType03` <>0)

There are 10 in all I only using the first segment now as follows:
I am now testing for this and it worked. So how do I get the where clause back in and how do I create a UNION between each set?
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType01 AS Account, ValueAmt01 AS Amount,  UserID FROM Transaction
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 20059768
Trying adding in one piece of the Where clause at a time until it errors out to try and narrow down the problem.
Does it work with the full Where clause directly in Access?
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 20059789
Add this first and then see if it accepts the command:
Where (CardAmt <> 0)

If that works, append the next part:
Where (CardAmt <> 0) or (CheckAmt <> 0)

And so on.
0
 

Author Comment

by:ruavol2
ID: 20059948
I just got this sequence to work. Man you guys are great. I am going to have to split the points between the 3 of you. Hope that is cool. Man this is great. Thanks for helping me keep the little ones fed! I have been really struggling with this stuff.

SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType01 AS Account, ValueAmt01 AS Amount,  UserID FROM Transaction
Where (CardAmt <> 0) or (CheckAmt <> 0) or (CashAmt <> 0) and (ValueType01 <>0)
UNION
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType02 AS Account, ValueAmt02 AS Amount,  UserID FROM Transaction
Where (CardAmt <> 0) or (CheckAmt <> 0) or (CashAmt <> 0) and (ValueType02 <>0)

I was jsut curios when you create an alias with a SQL statement above "Why could I not have used (Account <>0) instead of (ValueType02 <>0). Can you not use it in the same statement that it was declared???
0
 
LVL 28

Assisted Solution

by:bdreed35
bdreed35 earned 800 total points
ID: 20059991
It doesn't actually alias the field until it returns the dataset, and it applies the where clause before it returns the dataset.

Also, you may want to look at the fields in your where clause because you have the and's and or's:

Where (CardAmt <> 0) or (CheckAmt <> 0) or (CashAmt <> 0) and (ValueType01 <>0)

The Value type will only be applied with CashAmt.  That may be what you want, but I suspect that this is probably what you were hoping for:

Where (CardAmt <> 0 or CheckAmt <> 0 or CashAmt <> 0) and ValueType01 <>0
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question