Oracle Crystal Stored Procedure

[TheEntertainer]

I have a sql statement which I would like to alter into an Oracle Stored Procedure on an Oracle 7 database. Although I'm fairly good with sql and have worked in SQL Server prior to Oracle I can't get my head around how to construct Oracle SPs.

I attach an example below basically I want to replace the **** in the sql with whatever is entered by the user. Eventually I'd like to base a Crystal 8 report on it. Any chance of someone pointing me in the right direction.

The luck winner gets January 1st off from work!

SELECT
FIELD_1, FIELD_2, FIELD_3,
FIELD_4, FIELD_5, FIELD_6, FIELD_7, FIELD_8, null com_txt
FROM
TABLE_1,
TABLE_2
WHERE
TABLE_1.FIELD_1 = TABLE_2."ID" AND
TABLE_1." = 'THE' AND
TABLE_2."COD" = '****'

union all

SELECT
NULL sot,null field_2,to_date(null) dtes,
TABLE_1.FIELD_1, TABLE_2.FIELD_1, to_number(null) CHK_NUM, TABLE_2.FIELD_2 dte, TABLE_2.FIELD_3, TABLE_2.FIELD_4
FROM
TABLE_1,
TABLE_2
WHERE
TABLE_1.FIELD_1 = TABLE_2.FIELD_1 AND
TABLE_1.FIELD_2 = '****'

Thanks

 

[synapsevampire]

I don't think that you need a Union, and they typically have horrible performance, but I'll just address your user entered filter concern.

Crystal is VERY particular about how it uses Oracle Stored Procedures, and you'll want a package and a stored procedure, plus a parameter to pass the user entered data through. Please read the following:

http://support.crystaldecisions.com/library/kbase/articles/c2001157.asp

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com