What are options for when adding an SQL subquery in Crystal Reports?

[hallux]

This existing Crystal Report has a parameter option to run based on the time of the last modification of the order. Then all orders last modified during the previous x hours can be displayed. This worked until the modification date details were moved to a separate history table. Now, as before, there is still a need to only collect one record for each order, but so far it is pulling multiples with the left join used in CR.

What are the options to integrate this query with a subquery to collect the needed time details? Would adding a SQL command be a good method? Thank you, Hallux.
----------------------------------------------------------
CR Professional; Product Type: Full Product Version 10.0.0.533
Oracle 10G Enterprise Edition Release 10.2
----------------------------------------------------------

SELECT o.orders_no,
o.orders_due_dt,
oh.ordershist_modate,
oh.ordershist_motime,
oh.ordershist_serial
FROM orders o
LEFT OUTER JOIN
ordershist oh
ON o.orders_no = oh.orders_no
WHERE o.orders_no = 1
AND oh.ordershist_serial = (SELECT MIN (ordershist_serial)
FROM ordershist
WHERE ordershist.orders_no = 1)

table: ORDERS
ORDERS_NO ORDERS_DUE_DT ORDERS_DESC
1 10/01/2010 steel paperclip

table: ORDERSHIST
ORDERSHIST_SERIAL ORDERS_NO ORDERSHIST_MODATE ORDERSHIST_MOTIME ORDERSHIST_EDITNM
535 1 09/14/2010 1284482929 BJohnson
536 1 09/17/2010 1284738348 BJohnson
538 1 09/20/2010 1284991254 BJohnson
539 1 09/20/2010 1284991426 CAndrus

 

[lbass]

Don't you really want to be checking the most recent datetime in the history table? Wouldn't you also want to pull multiple orders at a time?

You could use a command like this:

SELECT o.orders_no,
o.orders_due_dt,
oh.ordershist_modate,
oh.ordershist_motime,
oh.ordershist_serial
FROM orders o
LEFT OUTER JOIN
ordershist oh
ON o.orders_no = oh.orders_no
AND oh.ordershist_motime =
(
SELECT Max(ordershist_motime)
FROM ordershist A
WHERE A.ordershist.orders_no = ordershist.orders_no
)

This removes the where clause from the main query (for speed) and allows the return of the most recent history record per order no.

-LB

 

[hallux]

Yes, the max is what is needed in the code.

So in transfering this entire query to a selection from Crystal, is this something that I can plug into Crystal or might it best be put into an Oracle view or stored procedure and reference from Crystal?

The modifications to the query were just what I needed, especially the speed improvement.

Thanks for your feedback.
-Hallux

 

[Turkbear]

Hi,
Make the report based on a that Command object instead of selecting any table(s), and it will operate directly on the Oracle engine and , as such, will perform just like a View would.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

As Turkbear says, you can just enter this query into the "Add Command" area (above the table list in the database expert). You would have to add the punctuation appropriate to Oracle--double quotes around table and field names. I also just noticed an error in the last line of the subquery, which should read (also with punctuation added):

(
SELECT Max("ordershist_motime")
FROM "ordershist" A
WHERE A."orders_no" = "ordershist"."orders_no"
)

-LB