Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL Expression returning too many results

I am trying to display the value of a field in one table as a caption in the report header. This caption is above a u2018descriptionu2019 field in the details section. When I run my report, I get the description field displaying 832 times; which is the number of rows found in my caption table. Here is the structure of the two tables:

Table 1: Code, Description

Table 2: Table, Name, Field Name, Caption

Here are the SQL Expressions that I have tried:

(SELECT Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

(SELECT DISTINCT Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

And

(SELECT TOP 1 Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

If I run these directly in my Db, I get correct results. If in Crystal, incorrect results. I have tried the following suppress on the Details: Table1.Code= previous (Table1.Code) which works UNLESS I have any summaries. Once I include a summary, my total ALWAYS factors in the suppressed, duplicate records.

I have now moved to try using parameters and a sub report for each caption; however my existing reports have sub reports that also need caption changes and I am not aware of a way to add a subreport to a subreport

Any ideas?

Not what you were looking for? View more on this topic or Ask a question