SQL Command

[princessinthetower]

Oracle 9, Crystal 9

I'm trying to use SQL commands for the first time. I've created the command, saved it to the repository, placed it on a report and linked it successfully to a table.

I can browse the fields of the command and see data but when I run the report, it is reading records 1 of 1 and completely stops responding or gives me the first linked value in the command table but no others.

Has anyone experienced this kind of behavior?

 

[bdreed35]

If you browse a field from the Command, it will return records that satisfy the SQL from the Command. When you link to another table, it will return records that satisfy the type of join you set up. By default, it is probably an equal join, which is returning records that match between your SQL Command and the table you linked it to.

I would verify the data in the two tables to ensure that the data your expecting is available in each table. I would also ensure that the field(s) that you are linking on have more than one match between them.

~Brian

 

[princessinthetower]

It is a left outer join. I tried linking the table itself and there are multiple matches. When I link the command based on that same table, there is only one and the report stops responding.

 

[princessinthetower]

Follow up. The report works only if I use the command as the driving table. Then 'Show SQL Query' displays the command with no reference to the other table. If I reverse the direction of the links, 'Show SQL Query' displays the select statement for the table not the command and the report fails.

I thought I could use combinations of tables and commands together, but it looks like I have to begin the report with a command as driving table. Is this correct or am I missing something?

 

[synapsevampire]

I don't mix and match these.

And I try to avoid using commands in general, rather opting for Views as they are available to ALL tools, as opposed to a proprietary Command object.

Why not just modify the Command to include the table?

-k

 

[princessinthetower]

I think I will have to include the table in the command, just confused about why everything I read says these can be combined on a report when that's not exactly the case.

My first choice is to use views, but right now I have insufficient privileges to create them. Thanks for the response