• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4046
  • Last Modified:

How do I get this sql expression to compile in Crystal Reports?

The following is my SQL Expression:
(select distinct count("x"."evid_id")
from "reptext" "x"
INNER JOIN  "evidence" "e" ON "e"."evid_id"="x"."evid_id"
INNER JOIN "dnaProfile" "d" ON "x"."result_id"="d"."Result_id"
where "x"."req_id" = "REPTEXT"."req_id"
)

"REPTEXT"."req_id" was inserted when I double-clicked the field from the main report in the SQL Expression editor, so it is in the main report. However, this is the error I keep getting when I check the formula:
Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server
Driver][SQL Server]The multi-part identifier "REPTEXT.req_id" could not
be bound. [Database Vendor Code: 4104 ]'.
0
cheyanne040277
Asked:
cheyanne040277
  • 12
  • 6
  • 6
  • +1
1 Solution
 
BadotzCommented:
where "x"."req_id" = "REPTEXT"."req_id"

doesn't make sense, since you've aliased "reptext" as "x", so the SQL is equivalent to:

where "x"."req_id" = "x"."req_id"

I assume "REPTEXT"."req_id" is a variable you want to substitute?

It looks like too many quotes, too. Try:

(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = [REPTEXT.req_id]
)
0
 
BadotzCommented:
And

[REPTEXT.req_id]

would be changed to whatever value you really want.
0
 
cheyanne040277Author Commented:
x.req_id is my value in the subquery, but my thought process is that I need to correlate it to the main report SQL query, and so I wanted it to equal the reptext.req_id value in the main report sql query. So I aliased the reptext table in my subquery as x, so it would recognize it as a different table than the Reptext in the main SQL query. Is that right?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cheyanne040277Author Commented:
I took out the last inner join, so I could get it all in and show you the entire SQL statement. My problem is, now I get 41 records rather than 12, so there is something I'm missing:

 SELECT "DNAPROFILE"."CSF1PO", "DNAPROFILE"."TPOX", "DNAPROFILE"."THO1", "DNAPROFILE"."D16S539", "DNAPROFILE"."D7S820", "DNAPROFILE"."D13S317", "DNAPROFILE"."D21S11", "DNAPROFILE"."D8S1179", "EVIDENCE"."evid_num", "EVIDENCE"."origin", "DNAPROFILE"."D3S1358", "DNAPROFILE"."D2S1338", "DNAPROFILE"."D19S433", "DNAPROFILE"."vWA", "DNAPROFILE"."D18S51", "DNAPROFILE"."Amelogenin", "DNAPROFILE"."D5S818", "DNAPROFILE"."FGA", "REPTEXT"."req_id", (select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
), "EVIDENCE"."descript"
 FROM   ((("sqllims"."dbo"."requests" "REQUESTS" INNER JOIN "sqllims"."dbo"."req_evid" "REQ_EVID" ON "REQUESTS"."req_id"="REQ_EVID"."req_id") INNER JOIN "sqllims"."dbo"."evidence" "EVIDENCE" ON "REQ_EVID"."evid_id"="EVIDENCE"."evid_id") INNER JOIN "sqllims"."dbo"."reptext" "REPTEXT" ON "EVIDENCE"."evid_id"="REPTEXT"."evid_id") INNER JOIN "sqllims"."dbo"."dnaProfile" "DNAPROFILE" ON "REPTEXT"."result_id"="DNAPROFILE"."Result_id"
 WHERE  "REPTEXT"."req_id"='ZCSD62HF0J3VFZ'
0
 
cheyanne040277Author Commented:
Sorry, I took out the Where clause, not the inner join.
0
 
mlmccCommented:
Crystal uses { } to get at fields.  Try that

mlmcc
0
 
BadotzCommented:
>>Is that right?

Not the way you specified it. You will have to add a reference to the main form, something like this:

where x.req_id = [YOUR_MAIN_FORM_NAME]![REPTEXT.req_id]

And until the syntax is correct, you might consider:

where x.req_id = A_KNOWN_VALUE_FOR_THE ID

Note that if the id is not numeric, it must be enclosed in quotes:

where x.req_id = " A_KNOWN_VALUE_FOR_THE ID"
0
 
cheyanne040277Author Commented:
How do I know what [YOUR_MAIN_FORM_NAME] is for me? The known value works.
0
 
mlmccCommented:
It should be {TheFieldInTheDatabase}

mlmcc
0
 
cheyanne040277Author Commented:
I'm sorry I'm not understanding, but the field in the database is reptext.req_id.
0
 
BadotzCommented:
>>the field in the database is reptext.req_id

That may be, but you are referencing it TWICE. the VALUES for each reference should be different, and your syntax is wrong (as I stated earlier).
0
 
cheyanne040277Author Commented:
So it should be:

(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = [{REPTEXT.REQ_ID}]!["REPTEXT"."req_id"]
)

?
I don't understand what the syntax should be. I want to say that the x.req_id needs to be the same as the reptext.req_id in the main sql.
0
 
mlmccCommented:
(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = {REPTEXT.REQ_ID}
)

mlmcc
0
 
cheyanne040277Author Commented:
I get the following with that statement:

Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server
Driver]Syntax error or access violation'.

I ran this whole query in SQL Analyzer:
 SELECT "DNAPROFILE"."CSF1PO", "DNAPROFILE"."TPOX", "DNAPROFILE"."THO1", "DNAPROFILE"."D16S539", "DNAPROFILE"."D7S820", "DNAPROFILE"."D13S317", "DNAPROFILE"."D21S11", "DNAPROFILE"."D8S1179", "EVIDENCE"."evid_num", "EVIDENCE"."origin", "DNAPROFILE"."D3S1358", "DNAPROFILE"."D2S1338", "DNAPROFILE"."D19S433", "DNAPROFILE"."vWA", "DNAPROFILE"."D18S51", "DNAPROFILE"."Amelogenin", "DNAPROFILE"."D5S818", "DNAPROFILE"."FGA", "REPTEXT"."req_id",
(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = {REPTEXT.REQ_ID}
), "EVIDENCE"."descript"
 FROM   ((("sqllims"."dbo"."requests" "REQUESTS" INNER JOIN "sqllims"."dbo"."req_evid" "REQ_EVID" ON "REQUESTS"."req_id"="REQ_EVID"."req_id") INNER JOIN "sqllims"."dbo"."evidence" "EVIDENCE" ON "REQ_EVID"."evid_id"="EVIDENCE"."evid_id") INNER JOIN "sqllims"."dbo"."reptext" "REPTEXT" ON "EVIDENCE"."evid_id"="REPTEXT"."evid_id") INNER JOIN "sqllims"."dbo"."dnaProfile" "DNAPROFILE" ON "REPTEXT"."result_id"="DNAPROFILE"."Result_id"
 WHERE  "REPTEXT"."req_id"='ZCSD62HF0J3VFZ'

and received the following errors in SQL:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'sqllims'.
0
 
mlmccCommented:
You can't run it in SQL Analyzer.  SQL Analyzer doesn't know about the other query or the fields in it.

You are not using what Crystal calls a SQL Expression.

What field in the query is this going to link to?

It is more like this in SQL

(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = "REPTEXT"."REQ_ID") AS EvidIdCount,

mlmcc
0
 
cheyanne040277Author Commented:
The query I ran in sql is the whole query, not just my subquery. I inserted my subquery into the main crystal query and ran it in Query Analyzer.

The field that it is linking to in the main query is the reptext.req_id. Right now, I have it as the actual value, so if I go to Database, Show SQL Query in Crystal for the whole report, I get the following statement. You can see my SQL expression in the subquery in the middle. How do I get the id in the subquery to equal the reptext.req_id that is in the main query at the very bottom of the statement?
 SELECT "DNAPROFILE"."CSF1PO", "DNAPROFILE"."TPOX", "DNAPROFILE"."THO1", "DNAPROFILE"."D16S539", "DNAPROFILE"."D7S820", "DNAPROFILE"."D13S317", "DNAPROFILE"."D21S11", "DNAPROFILE"."D8S1179", "EVIDENCE"."evid_num", "EVIDENCE"."origin", "DNAPROFILE"."D3S1358", "DNAPROFILE"."D2S1338", "DNAPROFILE"."D19S433", "DNAPROFILE"."vWA", "DNAPROFILE"."D18S51", "DNAPROFILE"."Amelogenin", "DNAPROFILE"."D5S818", "DNAPROFILE"."FGA", "REPTEXT"."req_id",
(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = 'ZCSD62HF0J3VFZ'
)
, "EVIDENCE"."descript"
 FROM   ((("sqllims"."dbo"."requests" "REQUESTS" INNER JOIN "sqllims"."dbo"."req_evid" "REQ_EVID" ON "REQUESTS"."req_id"="REQ_EVID"."req_id") INNER JOIN "sqllims"."dbo"."evidence" "EVIDENCE" ON "REQ_EVID"."evid_id"="EVIDENCE"."evid_id") INNER JOIN "sqllims"."dbo"."reptext" "REPTEXT" ON "EVIDENCE"."evid_id"="REPTEXT"."evid_id") INNER JOIN "sqllims"."dbo"."dnaProfile" "DNAPROFILE" ON "REPTEXT"."result_id"="DNAPROFILE"."Result_id"
 WHERE  "REPTEXT"."req_id"='ZCSD62HF0J3VFZ'

0
 
BadotzCommented:
Something like this, perhaps:

...
(select distinct count(x.evid_id)
from reptext x
INNER JOIN  evidence e ON e.evid_id=x.evid_id
INNER JOIN dnaProfile d ON x.result_id=d.Result_id
where x.req_id = 'ZCSD62HF0J3VFZ'
) XX
, "EVIDENCE"."descript"
 FROM   ((("sqllims"."dbo"."requests" "REQUESTS" INNER JOIN "sqllims"."dbo"."req_evid" "REQ_EVID" ON "REQUESTS"."req_id"="REQ_EVID"."req_id") INNER JOIN "sqllims"."dbo"."evidence" "EVIDENCE" ON "REQ_EVID"."evid_id"="EVIDENCE"."evid_id") INNER JOIN "sqllims"."dbo"."reptext" "REPTEXT" ON "EVIDENCE"."evid_id"="REPTEXT"."evid_id") INNER JOIN "sqllims"."dbo"."dnaProfile" "DNAPROFILE" ON "REPTEXT"."result_id"="DNAPROFILE"."Result_id"
 WHERE  "REPTEXT"."req_id"='ZCSD62HF0J3VFZ'
AND XX.req_id = "REPTEXT"."req_id"
0
 
cheyanne040277Author Commented:
Well, in Crystal I cannot add that AND statement at the end, since I xx.req_id only exists as a SQL expression.
0
 
BadotzCommented:
Even if you alias it as XX ??
0
 
cheyanne040277Author Commented:
Let me ask this a different way, I think I'm confusing myself. If you look at the SQL statement below, I can run this just fine in SQL Query Analyzer. If you look at the subquery, that is what I am pasting in as a SQL Expression in Crystal but I get the error.

Query runs in SQL:
SELECT "DNAPROFILE"."CSF1PO", "DNAPROFILE"."TPOX", "DNAPROFILE"."THO1", "DNAPROFILE"."D16S539", "DNAPROFILE"."D7S820", "DNAPROFILE"."D13S317", "DNAPROFILE"."D21S11", "DNAPROFILE"."D8S1179", "EVIDENCE"."evid_num", "EVIDENCE"."origin", "DNAPROFILE"."D3S1358", "DNAPROFILE"."D2S1338", "DNAPROFILE"."D19S433", "DNAPROFILE"."vWA", "DNAPROFILE"."D18S51", "DNAPROFILE"."Amelogenin", "DNAPROFILE"."D5S818", "DNAPROFILE"."FGA", "REPTEXT"."req_id",

(select distinct count(x.evid_id)
            from reptext x
            INNER JOIN  evidence e
            ON e.evid_id=x.evid_id
            INNER JOIN dnaProfile d
            ON x.result_id=d.Result_id
            where x.req_id = "REPTEXT"."req_id"
)

, "EVIDENCE"."descript"
 FROM   ((("sqllims"."dbo"."requests" "REQUESTS" INNER JOIN "sqllims"."dbo"."req_evid" "REQ_EVID" ON "REQUESTS"."req_id"="REQ_EVID"."req_id") INNER JOIN "sqllims"."dbo"."evidence" "EVIDENCE" ON "REQ_EVID"."evid_id"="EVIDENCE"."evid_id") INNER JOIN "sqllims"."dbo"."reptext" "REPTEXT" ON "EVIDENCE"."evid_id"="REPTEXT"."evid_id") INNER JOIN "sqllims"."dbo"."dnaProfile" "DNAPROFILE" ON "REPTEXT"."result_id"="DNAPROFILE"."Result_id"
 WHERE  "REPTEXT"."req_id"='ZCSD62HF0J3VFZ'

Error:
Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server
Driver][SQL Server]The multi-part identifier "REPTEXT.req_id" could not
be bound. [Database Vendor Code: 4104 ]'.
0
 
mlmccCommented:
In the Crystal Expression you have to use the Crystal syntax

select distinct count(x.evid_id)
            from reptext x
            INNER JOIN  evidence e
            ON e.evid_id=x.evid_id
            INNER JOIN dnaProfile d
            ON x.result_id=d.Result_id
            where x.req_id = {"REPTEXT"."req_id"}

Or better yet

select distinct count(x.evid_id)
            from reptext x
            INNER JOIN  evidence e
            ON e.evid_id=x.evid_id
            INNER JOIN dnaProfile d
            ON x.result_id=d.Result_id
            where x.req_id =  XXXXX

Select the field from the field list in the formula editor where the XXXXX is

mlmcc
0
 
cheyanne040277Author Commented:
If I double-click the field name I get it puts the field name in without brackets:
(select distinct count(x.evid_id)
            from reptext x
            INNER JOIN  evidence e
            ON e.evid_id=x.evid_id
            INNER JOIN dnaProfile d
            ON x.result_id=d.Result_id
            where x.req_id =  "REPTEXT"."req_id"
)

When I check the statement I get the following error:
Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server
Driver][SQL Server]The multi-part identifier "REPTEXT.req_id" could not be bound. [Database Vendor Code: 4104 ]

If I manually add the brackets so it is:
(select distinct count(x.evid_id)
            from reptext x
            INNER JOIN  evidence e
            ON e.evid_id=x.evid_id
            INNER JOIN dnaProfile d
            ON x.result_id=d.Result_id
            where x.req_id =  {"REPTEXT"."req_id"}
)

I get the following error:
Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server
Driver]Syntax error or access violation'.
0
 
mlmccCommented:
I haven't used SQL expressions and don't have an SQL database to try building one against.

mlmcc
0
 
James0628Commented:
I don't generally use SQL expressions, but from the experimenting that I just did, I don't see any way to get what you're trying to do to work.  Contrary to what mlmcc said, in the SQL expression you have to use the database's syntax, not CR's, so you were OK there.

 The problem is that CR appears to be checking the SQL expression by itself, without putting it into the context of the main query on the report.  I could not find a way to get CR to accept a SQL expression that used a field that was in the main query and not in the SQL expression.  I may be missing something, but I can't see a way to tie the SQL expression to a specific value in the main query.  In my tests, I had the main report reading a list of groups in a company and was trying to use a SQL expression to get a count of the customers in each group.  I can't see any way to get that to work.


 Do you really need a subquery to get that count?  Can you get it from the main query instead?  Just thought I'd ask.

 One alternative is to use a subreport to get the counts.  If you just need to see the count on the report, the subreport can output the count at the end.  If you need to do more with the counts, the subreport can save the count in a shared variable and you can then use that variable in the main report.

 If you'd like some more details on the subreport idea, just holler.  :-)

 James
0
 
cheyanne040277Author Commented:
Thanks for verifying what I thought, I will use a subreport. Thank you.
0
 
James0628Commented:
You're welcome.  Sorry it wasn't better news.

 James
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now