Link options

[smibarb]

I suspect what I am trying to achieve may have to do with how I link tables, but I am very weak on this topic.

I have an Operator table, a table for Patient_results and a table for Control_results. Operator ID is the field linking the Operator table to the Patient table and the operator table to the Control table. The two links are the default type (inner join, not enforced, =). There is no relationship between the patient result table and the control table. I want a report that shows all the results for an operator ID in a given period; both patient and control results. A detail line in the report should have a line for each control result (with nothing in the patient result field) and a line for each patient result (with nothing in the contol result field). Of course, I am getting loopy results as the detail line tries to put both control and patient results on a detail line and there is no relationship.

I am using CR ver 11.

 

[synapsevampire]

You're probably experiencing row or record inflation.

And there is a relationship between control and patient, it's the operator.

If you only expect one each of the control and patient values per operator, group by the operator and then use formulas to get the maximum of each value:

maximum({table.patient},{table.operator})

the otehr would be something like:

maximum({table.control},{table.operator})

Understanding how databases work is complex, and there is no ONE way to desgin them. Either your dba or your database vendor shoudl be able to explain the proper query to return what you want.

-k

 

[smibarb]

Thanks for the suggestion but I expect numerous control values and numerous patient values for each operator.

What I meant by "no relationship between the patient result table and the control result table" (besides the noted relationship of the operator who performed the test) was that a patient result does not have a corresponding control result. Therefore, on each detail line of the report I would like either a patient result and date performed or a control result and date performed.

Unfortunately the database vendor does not support Crystal Reports. Any additional suggestions appreciated.

 

[lbass]

This is an obvious candidate for a subreport. Remove one of the tables that you are linking to from the Operator table and use it in a subreport. Group by Operator ID and place the subreport in the group footer, linking it to the main report by Operator ID. Use the fields from the main report table in the detail section.

-LB

 

[synapsevampire]

Since you're using CR XI, you can generate your own SQL in a COMMAND (listed 1st under the connection) which would be something like:

select 'patient' MySource, <additional field list> from operator, patient
where operator.opid = patient.opid
UNION ALL
select 'control' MySource, <additional field list> from operator, control
where operator.opid = control.opid

This will return the rows with a field called MySource to differentiate them, so you can sort on that.

There are other means as well, this being the simpler.

Note that you must return matching data types in each field list, so if you have some fields that don't exist in either query, hardcode values as I did for MySource.

You could also use a subreport, and in the main report only join the patient table, group by the operator, and create a subreport (link by Operator) in the group footer that joins only the control and the operator tables.

Then the patients would be listed first, then the subreport would list the controls.

-k

 

[smibarb]

I was hoping to avoid subreports as I want to be able to manipulate and do formulas on the results.

I will attempt the Command, but I am a novice so I need to ask for clarification, I must be getting the syntax wrong.

I have (exactly):

SELECT 'patient'MySource

op_t.op_id
op_t.op_name
rslt_pat_t.rslt_date_time
rslt_pat_t.rslt

WHERE op_t.op_id=rslt_pat_t.op_id

UNION ALL

SELECT 'control'Source

op_t.op_id
op_t.op_name
rslt_qc_t.rslt_date_time
rslt_qc_t.rslt

WHERE op_t.op_id=rslt_qc_t.op_id

I get the following errors:

Crystal Reports
---------------------------
Failed to retrieve data from the database.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Line 2: Incorrect syntax near 'op_t'.
SQL State: 42000
Native Error: 170 [Database Vendor Code: 170 ]
---------------------------
OK
---------------------------
and then

the same error for line 9. So for some reason it does not like the op_t.op_id line in either spot.

Synasevampire, I may have misinterpreted what you meant in your example . I interpreted literally that I put "'patient'MySource which will become the new field name (?) I may have also misunderstood what you meant by "Note that you must return matching data types in each field list, so if you have some fields that don't exist in either query, hardcode values as I did for MySource.", I thought I was okay as the field names/types are the same from both the patient and control table.

This is all brand new territory for me so thanks in advance for your patience.

 

[lbass]

You need commas between your fields, and you didn't add in the FROM clause with your tables. To see how you should do this, take a look at another report using the same datasource and go to database->show SQL Query and observe the syntax.

-LB

 

[smibarb]

Thank you synapsevampire for your suggestion to do the Command and thank you lbass for the suggestion regarding the syntax; I am close but am still not there. However I finally "get" what this will do for me and have tested the results and am getting the expected data returned. (It is considerably slower than pulling a report without a command though).

Now that I see what I am doing I realize that I do need to add a field to my command that only appears in the qc table and not in the pat table. I believe this was addressed by synapsevampire: "Note that you must return matching data types in each field list, so if you have some fields that don't exist in either query, hardcode values as I did for MySource" However, I am unclear as to what this means. (I had to remove the text 'patient'MySource and 'control'MySource (detailed in synapsevampire's example) from my command as I could not figure out the right syntax and it seems to work fine without it - if this is necessary, or an example of what "hardcoding" is, please provide suggestion for syntax.

Secondly, if I am successful in getting this field in my command table, I would like to link this field to a third table in my report. I tried to do something similar and it appears that I cannot do any linking other that the SQL command as I get a warning:

Database Warning
---------------------------
More than one datasource or a stored procedure has been used in this report.
Please make sure that no SQL Expression is added and no server-side group-by is performed.
---------------------------
OK

So I gather that all linking needs to be done in the command object?

Rather wordy, I apologise. So bottom line questions are:
1)How do I add a field from one table that does not have a corresponding filed in the other table?
2)Does having a command mean that all linking of tables needs to occur in the command?


Here is my command:

SELECT

"op_t"."op_id",
"op_t"."op_name",
"rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."rslt"

FROM "co_main"."dbo"."op_t", "co_main"."dbo"."rslt_pat_t"

WHERE "op_t"."op_id"= "rslt_pat_t"."op_id"

UNION ALL

SELECT

"op_t"."op_id",
"op_t"."op_name",
"rslt_qc_t"."rslt_date_time",
"rslt_qc_t"."rslt"

FROM "co_main"."dbo"."op_t", "co_main"."dbo"."rslt_qc_t"

WHERE "op_t"."op_id"= "rslt_qc_t"."op_id"

 

[lbass]

I think the report will be faster if you don't link with a table outside the command, although this is fine to do.

What datatype is the field you want to add? You can use a 0 in the corresponding (sequentially) field for a number field, a word like 'text' for a string field, or a literal date for a datefield (the syntax depends upon your datasource, so you could check "Show SQL Query" from another report to see whether it should appear like '25-Oct-05' or some other format. You should be able to add SV's suggested text to each half of the query--it will help you differentiate the two. Try leaving a space or add "as" in between, as in:

'Patient' as Source

'Control' as Source

Be sure to use single quotes.

If you are working with several tables, it might be easier to start creating the report the usual way, with all tables except the one you want to use in the union statement. Then you could copy the SQL query from "Show SQL query" and use that to begin the union all statement in a new report where you use Add Command instead of tables. You would then add Union all at the end of the pasted query, copy the query again and change the particular fields and table names that should come from the new table, leaving other tables/field names in place.

-LB

 

[smibarb]

Thank you, I have successfully added my number field and succeeded in adding my text labels to distinguish origin!I am very grateful for the suggestion as I have learned much in this exercise that I will be able to apply elsewhere.

This will allow me to extract the information I require but it is extremelyslow and is even slower when I link fields from other tables. Every time I add a field or edit something it goes and re-reads all the records all over again. Not just the 290 records for the selected operator but all 4,000,000 records, each time I have to add or edit a field. This is very frustrating as the command has created a union table with the correct data and if it behaved like a regular table I would be laughing!

I was unable to successfully create a SQL query by pasting the Union All query at the end of my other query - it does not reject the joint query but seems to stop and not "see" the set of select criteria for the union all.

I also tried creating two sets of commands and linking them as two tables. This ground the system to a halt.

Any additional suggestions?

 

[synapsevampire]

First, you removed "I had to remove the text 'patient'MySource and 'control'MySource (detailed in synapsevampire's example) from my command ". Check your code, you didn't have MySource for each, you had Source for the second select, the first rule stated that they must match.

If you are changing a query, then the database will be queried again, this has nothing to do with using a Command.

A Command just passes SQL, just as the Crystal GUI does optimally. It may be slower than returning data that isn't correct, but think about that, you'ree not returning what is needed but the performance is better.

You can use the following query and beat both:

select 'hi' data from table.

Won't query a thing and it'll be very fast!

Try running this query in your database query tool:

SELECT
'patient' MySource,
"op_t"."op_id",
"op_t"."op_name",
"rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."rslt"
FROM
"co_main"."dbo"."op_t",
"co_main"."dbo"."rslt_pat_t"
WHERE "op_t"."op_id"= "rslt_pat_t"."op_id"
UNION ALL
SELECT
'control' MySource,
"op_t"."op_id",
"op_t"."op_name",
"rslt_qc_t"."rslt_date_time",
"rslt_qc_t"."rslt"
FROM
"co_main"."dbo"."op_t",
"co_main"."dbo"."rslt_qc_t"
WHERE "op_t"."op_id"= "rslt_qc_t"."op_id"

If this fails, and "it does not reject the joint query but seems to stop and not "see" the set of select criteria for the union all.", please try rephrasing this to describe what occurs, explain what is returned, the MySource field will indicate what was returned.

You can also just try it in a Crystal Command.

-k

 

[smibarb]

I did successfully set up the query as you described. Prior to your last post I noticed my mistake with the MySource and had successfully created the Union All query with the 'patient' and 'control' text labels and was also able to successfully add a field that only occurred in the qc table. I did report this success in my last post of 15 Nov 05 18:41. Sorry synapsevampire , perhaps I could have been clearer.

This created exactly the table I needed (Thanks again). I had hoped to then link this table with several other tables in the regular manner, and carry on with my report. In my last post what I was trying to say is that as soon as I link this table (which contains the info I want), it does not seem to perform like a "regular" table (for example, as how the same report behaves with using either the rslt_pat_table or the rslt_qc_table instead of the Command table created from the Query). Each time I add a field to the report it re-reads 4,000,000 records quite slowly which makes the buiding process go very slowly.

What I was referring to when I said "it does not reject the joint query but seems to stop and not "see" the set of select criteria for the union all." was lbass' suggestion at 15 Nov 05 14:32 that stated "If you are working with several tables, it might be easier to start creating the report the usual way, with all tables except the one you want to use in the union statement. Then you could copy the SQL query from "Show SQL query" and use that to begin the union all statement in a new report where you use Add Command instead of tables. You would then add Union all at the end of the pasted query, copy the query again and change the particular fields and table names that should come from the new table, leaving other tables/field names in place." When I tried this suggestion it did not seem to utilize both queries (by both I mean the Union all query and the SQL query used for the other data).

My Union All query appears exactly as you have listed in your last post. I am not sure what you mean by:

"select 'hi' data from table.Won't query a thing and it'll be very fast!"


This is an example of the "other" SQL query that is from another report that uses only qc_table instead of the command table formed by the UNION ALL query.


SELECT "op_t"."op_id", "op_t"."op_name", "op_recert_t"."in_service_date", "op_recert_t"."op_recert_date", "nu_t"."nu_name", "op_t"."deleted_flg", "op_t"."new_op_flg", "rslt_qc_t"."rslt_date_time", "rslt_qc_t"."rslt", "rslt_qc_t"."rslt_type_id", "rslt_type_t"."rslt_type_desc", "op_t"."nu_sys_id", "nu_t"."site_abbrev", "rslt_qc_t"."procedure_err"
FROM ((("co_main"."dbo"."op_t" "op_t" LEFT OUTER JOIN "co_main"."dbo"."rslt_qc_t" "rslt_qc_t" ON "op_t"."op_sys_id"="rslt_qc_t"."op_sys_id") INNER JOIN "co_main"."dbo"."op_recert_t" "op_recert_t" ON "op_t"."op_sys_id"="op_recert_t"."op_sys_id") LEFT OUTER JOIN "co_main"."dbo"."nu_t" "nu_t" ON "op_t"."nu_sys_id"="nu_t"."nu_sys_id") INNER JOIN "co_main"."dbo"."rslt_type_t" "rslt_type_t" ON "rslt_qc_t"."rslt_type_id"="rslt_type_t"."rslt_type_id"
WHERE ("rslt_qc_t"."rslt_date_time">={ts '2004-11-01 00:00:01'} AND "rslt_qc_t"."rslt_date_time"<{ts '2005-11-01 00:00:00'}) AND "op_t"."new_op_flg"=0 AND "rslt_type_t"."rslt_type_desc"='Unknown A' AND "op_t"."op_name" NOT LIKE 'INSTRUCTOR%' AND "op_recert_t"."in_service_date"<{ts '2004-11-01 00:00:01'} AND "rslt_qc_t"."procedure_err"=0