Sub-Query Help needed

[alexfromuno]

I'm running Crystal 8.5,
Oracle 8.1.7 and can't quite get a sub-query to work in Crystal ( I guess I don't know what I'm doing wrong)

Here's the SQL statement:

select aa.PAT_ID,pd.LAST_NAME,pd.FIRST_NAME, pt.DATE_OF_TRANS, aa.ACTION_SCHEDULED_DATE
from actions aa,
patient_demo pd,
pat_transplant pt
where aa.ACT_ID = 10165
and aa.PAT_ID = pd.PAT_ID
and aa.PAT_ID = pt.PAT_ID
and pt.DATE_OF_TRANS = (select min(pt2.DATE_OF_TRANS)
from pat_transplant pt2
where pt2.ORGAN_TYPE = 4
AND PT2.PAT_ID = pt.pat_id)
order by aa.ACTION_SCHEDULED_DATE

It works fine in SQL dsigner, but, whenever I try and inport a .QRY into Crsytal 8.5 I see no fields to work with
or
when I attempt to cut up the above SQL into a more manageable part to use in Formula Editor I get weried error messages, like 'The ) is missing' and I can't locate the front (.

Anyone out there have an answer, please.
alex

 

[pgtek]

hi
try creating a storeprocedure with ur query or used a view
it should work.
sometime crystal sql syntax varies from MS SQL

cheers

pgtek

 

[alexfromuno]

pgtek,

OK,sounds kool!!!

How do I create a Storedprocedure for the sub-query, Please?

alex

 

[synapsevampire]

You can't paste SQL Server syntax into the Database->Shoiw SQL Query in Crystal 8.5

You can use an RDO/ADO connection though, but I would advise against that as well as the query is now embedded in the report.

From the Query Analyzer modify your SQL to:

create view vw_mydata as
select aa.PAT_ID,pd.LAST_NAME,pd.FIRST_NAME, pt.DATE_OF_TRANS, aa.ACTION_SCHEDULED_DATE
from actions aa,
patient_demo pd,
pat_transplant pt
where aa.ACT_ID = 10165
and aa.PAT_ID = pd.PAT_ID
and aa.PAT_ID = pt.PAT_ID
and pt.DATE_OF_TRANS = (select min(pt2.DATE_OF_TRANS)
from pat_transplant pt2
where pt2.ORGAN_TYPE = 4
AND PT2.PAT_ID = pt.pat_id)
order by aa.ACTION_SCHEDULED_DATE

Now you have a View you can use for the report.

The other option is to use a SQL Expression for the subquery, which is fairly complicated. Ignore PG's post, he must have misunderstood about the SQL syntax.

-k

 

[kskid]

I run CR 8.5 with Oracle 8i and here's what I have done.

If it works in SQL Designer, then cut the subquery portion and paste it in the Database->Show SQL Query at the end but before the order by statement


Normal CR SQL here...

and pt.DATE_OF_TRANS = (select min(pt2.DATE_OF_TRANS)
from pat_transplant pt2
where pt2.ORGAN_TYPE = 4
AND PT2.PAT_ID = pt.pat_id)

Order by or group by statement here...