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

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]
)

And

[REPTEXT.req_id]

would be changed to whatever value you really want.

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?

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'

Sorry, I took out the Where clause, not the inner join.

Crystal uses { } to get at fields.  Try that

mlmcc

>>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"

How do I know what [YOUR_MAIN_FORM_NAME] is for me? The known value works.

It should be {TheFieldInTheDatabase}

mlmcc

I'm sorry I'm not understanding, but the field in the database is reptext.req_id.

>>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).

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.

(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

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'.

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

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'

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"

Well, in Crystal I cannot add that AND statement at the end, since I xx.req_id only exists as a SQL expression.

Even if you alias it as XX ??

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 ]'.

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

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'.

I haven't used SQL expressions and don't have an SQL database to try building one against.

mlmcc

Thanks for verifying what I thought, I will use a subreport. Thank you.

You're welcome.  Sorry it wasn't better news.

 James