Oracle SQL Query in a Crystal Report - V10

[BeeBear]

Hi,

I am trying to create a SQL Query within a crystal report, using Oracle Sql, and am having trouble with it.

The query is to give me a summary table of data (one column in the output), to link into the CWRCASE Table in the Crystal Report, with one line per CASE_ID, showing the LATEST CDRDOCT.CDRDOCT


I have 2 problems that I cant solve:

1. When I 'check' the formula, it gives me the error message "ORA01427: Single Row subquery returns more than one row."

I think this is because its not recognising the link back from the subquery to the CDRDOCT.CDRDOCT_CASE_ID Field, in the main query level.

2. Also, I cant seem to get the query to link back to the table in the actual report "CWRCASE" without putting the table name in the "FROM" Section of the query, which then gives me the wrong data (eg same result for every line of the report, regardless of the "CWRCASE.CWRCASE_ID"
If I leave the table out of the FROM line, I get an "Invalid Table Name" error.


Please respond in simple terms as I am not an experienced Oracle SQL user.

The formula is:

(
Select CDRDOCT.CDRDOCT_DTYP_CODE
FROM
CWRCASE, CDRDOCT, CTRDTYP
Where
CTRDTYP.CTRDTYP_DISP_IND = 'F'
AND
CDRDOCT.CDRDOCT_DTYP_CODE = CTRDTYP.CTRDTYP_CODE
and
CDRDOCT.CDRDOCT_CASE_ID = "CWRCASE"."CWRCASE_CASE_ID"
And
CDRDOCT.CDRDOCT_SEQ_NO =

(Select Max(Doc.CDRDOCT_SEQ_NO)
From CDRDOCT Doc, CTRDTYP Type
Where
Type.CTRDTYP_DISP_IND = 'F'
AND
Doc.CDRDOCT_DTYP_CODE = Type.CTRDTYP_CODE
And
Doc. CDRDOCT_CASE_ID = CDRDOCT.CDRDOCT_CASE_ID
)
)

As you can see, the Subquery, links to the main query in the last line, and the whole SQL query links to the report with the "CWRCASE" table join. In another report, I can put this join in without having to add the "CWRCASE" Table to the FROM section.....

Any help would be fantastic!!




regards

BB

*** Count your blessings not your problems******

 

[IanWaterman]

Try

(
Select CDRDOCT.CDRDOCT_DTYP_CODE
FROM
CWRCASE, CDRDOCT, CTRDTYP,
(Select Max(Doc.CDRDOCT_SEQ_NO) Max_doc
From CDRDOCT Doc, CTRDTYP Type
Where
Type.CTRDTYP_DISP_IND = 'F'
AND
Doc.CDRDOCT_DTYP_CODE = Type.CTRDTYP_CODE
And
Doc. CDRDOCT_CASE_ID = CDRDOCT.CDRDOCT_CASE_ID
) md
Where

CDRDOCT.CDRDOCT_DTYP_CODE = CTRDTYP.CTRDTYP_CODE
and
CDRDOCT.CDRDOCT_CASE_ID = CWRCASE.CWRCASE_CASE_ID
And
CDRDOCT.CDRDOCT_SEQ_NO = md.max_doc
and
CTRDTYP.CTRDTYP_DISP_IND = 'F'
)

Ian

 

[BeeBear]

Hi,

Thanks for your help Ian.

Unfortunately the one above wouldnt work for me, but after a LOT of trial and error, I ended up using this formula:


(Select CDRDOCT_DTYP_CODE
From
CDRDOCT
where
CDRDOCT_SEQ_NO = (Select max(CDRDOCT_SEQ_NO) MAX_SEQ
From CDRDOCT Doc, CTRDTYP
Where
CDRDOCT_DTYP_CODE = CTRDTYP_CODE
and
CTRDTYP_DISP_IND = 'F'
and
CDRDOCT_CASE_ID = "CWRCASE"."CWRCASE_CASE_ID"
)
and
CDRDOCT_CASE_ID = "CWRCASE"."CWRCASE_CASE_ID"
)


I found that although I used the same table in the query AND the subquery (CDRDOCT), if I put an alias table name in either query, the sql wouldnt work and gave me "Invalid Table Name" errors.
I also didnt have to join the 2 tables together in the Where clause to get the right data!

Just thought I'd post the result in case anyone else has the same issue...


I also copied the same SQL to get the CDRDOCT_DATE instead of the CDRDOCT_DTYP_CODE.


regards

BB

*** Count your blessings not your problems******