cancel
Showing results for 
Search instead for 
Did you mean: 

Linking SQL Server Stored Procedures

Former Member
0 Kudos

Post Author: RiddlerMN

CA Forum: Data Connectivity and SQL

I am trying to link two "tables" from SQL Server 2005 stored procedures in a one-to-many relationship in CR XI-R2. I manually setup a link between the two "tables" using the Database Expert. The problem is, the report will only include 1 row out of the master "table". The rows from the detail "table" are included correctly. I am not doing any filtering of the data returned by the stored procedures in CR. However, if I do add a filter to the master "table" results, I will get the correct specified master row plus the proper detail information. But I can't seem to ever get more than 1 row from the master "table" no matter what I do. Any ideas??

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Post Author: RiddlerMN

CA Forum: Data Connectivity and SQL

Using a Command for both seems to do the trick. I tried the OUTER JOIN and noticed that I did get multiple master rows but it was missing detail data. Not sure why this wasn't working but the Command works great so, I'll go that route.

Thanks a lot for your help! I appreciate it.

Former Member
0 Kudos

Post Author: V361

CA Forum: Data Connectivity and SQL

You can add a command as a data source and put the SQL in the command, you can edit the SQL this way. It will improve performance on large tables to use a command since "In most cases" the database stuff will be done on the server instead of the box CR is running on. Also try this type of join Left outer join

The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the resultu2014but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

For example, this allows us to find an employee's department, but still to show the employee even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments get filtered out).

Example of a left outer join(new):SELECT *

FROM employee

LEFT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

Employee.LastName

Employee.DepartmentID

Department.DepartmentName

Department.DepartmentID

Jones

33

Engineering

33

Rafferty

31

Sales

31

Robinson

34

Clerical

34

Smith

34

Clerical

34

Jasper

36

NULL

NULL

Steinberg

33

Engineering

33

Former Member
0 Kudos

Post Author: RiddlerMN

CA Forum: Data Connectivity and SQL

Thanks V361 for looking at this.

Here is the SQL. I have two INT parameters I am passing into both stored procs.

sql01"Database1"."dbo"."Report_LeasedVerificationCallsNeeded_CustomerGroup";1 28, 42 EXTERNAL JOIN CustomerGroup.CustId={?sql01: PropertyDetail.CustId}

sql01"Database1"."dbo"."Report_LeasedVerificationCallsNeeded";1 28, 42 EXTERNAL JOIN PropertyDetail.CustId={?sql01: CustomerGroup.CustId}

I am not sure what you mean by "Command"?

Former Member
0 Kudos

Post Author: V361

CA Forum: Data Connectivity and SQL

can you post the SQL from CR, are you using a Command ?