Pass date variable to Oracle SQL view through Crystal

[Maven4Champ]

Not sure if this is possible or not.

I have a view which I would like to have in the WHERE Clause a StartDate and EndDate variable that would prompt when you execute the query. I would like to get that far first to see if this works.

If it does, my next step would be to utilize Crystal Reports 8.5 to prompt the user in our VB application for a Start Date and End Date which is subsequently passed into the where clause of the view and restrics the data coming back into the report.

So my first question - is it possible to pass date variables to an Oracle SQL view and if so, what is the proper syntax?

Second question, If I build a Crystal report based off this view, will it automatically prompt in the report for a date range or is there further work involved?

Let me know.
Thanks,
Mike

 

[Turkbear]

Hi,
Views cannot be built with variables .
Use a Stored Procedure instead..






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

 

[Maven4Champ]

I am using a stored procedure now (or attempting to) and have found this KB article on the Crystal Business Objects site about using Crystal and Oracle Stored Procedures.

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

But before I get to that, I need to get my stored procedure to parse correctly. Here is my syntax thus far:

CREATE OR REPLACE PROCEDURE SP_C2C_ROLLUP (StartDate DATE, EndDate DATE)
AS
BEGIN
SELECT (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME) DEALER_CODE_ROLLUP,
		V_LIFE_OF_A_DEAL.REGION, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_DEAL) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_DLR_AVG, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_REHASH) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_REHASH, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_TO_GEN) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT_TO_GEN, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_GEN_TO_RECVD) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_GEN_TO_RECVD, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_RECVD_TO_FUNDED) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_RECVD_T0_FUND, 
       COUNT(V_LIFE_OF_A_DEAL.APP_NUM) AS QTY 
  FROM SIEBEL.V_LIFE_OF_A_DEAL 
 WHERE TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) >= 'StartDate' AND TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) <= 'EndDate'
 GROUP BY (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME), V_LIFE_OF_A_DEAL.REGION;
END;

This fails however with the reason of needing a SELECT INTO statement instead of just a SELECT statement. I'm not a stored procedure guru, especially when it comes to syntax. I am basically trying to follow the article I posted above and my syntax and trying to get this to parse in TOAD or SQL PLUS first. From there, if I can follow along with the Crystal KB article and pass my parameters to the Oracle SP, I should be good to go.

Any ideas - am I missing alot of syntax to properly pass variables (dates) to my SP?

 

[Maven4Champ]

I should mention I am also using the following package:

CREATE OR REPLACE PACKAGE CR_PACKAGE
AS TYPE CR_TYPE IS REF CURSOR RETURN SIEBEL.V_LIFE_OF_A_DEAL%ROWTYPE;
END CR_PACKAGE;
/

This is per the Crystal Reports documentation I listed above. Not sure what the purpose of this is as again I am new to procedures but I will obviously need to add this to my Procedure SQL once I get it to parse so it is usable with Crystal reports. I belive the package/cursor) is needed because I am returning multiple rows of data instead of just one row?

 

[Maven4Champ]

OK I think I am getting closer. Here is my revised code using the package but it still fails with the failure code below:

CREATE OR REPLACE PROCEDURE SP_C2C_ROLLUP
(CR_CURSOR IN OUT CR_PACKAGE.CR_TYPE,
StartDate IN SIEBEL.V_LIFE_OF_A_DEAL.FUND_DATE%TYPE,
EndDate IN SIEBEL.V_LIFE_OF_A_DEAL.FUND_DATE%TYPE)
AS
BEGIN
OPEN CR_CURSOR FOR
SELECT (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME) DEALER_CODE_ROLLUP,
		V_LIFE_OF_A_DEAL.REGION, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_DEAL) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_DLR_AVG, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_REHASH) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_REHASH, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_TO_GEN) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT_TO_GEN, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_GEN_TO_RECVD) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_GEN_TO_RECVD, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_RECVD_TO_FUNDED) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_RECVD_T0_FUND, 
       COUNT(V_LIFE_OF_A_DEAL.APP_NUM) AS QTY 
  FROM SIEBEL.V_LIFE_OF_A_DEAL 
 WHERE TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) >= '@StartDate' AND TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) <= '@EndDate'
 GROUP BY (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME),
			V_LIFE_OF_A_DEAL.REGION;
END SP_C2C_ROLLUP;
/

It fails at line 8 (right where my SELECT statement begins) with the following error: PLS-00382: expression is of wrong type

-- So I think I have come to this. Do I need to define this field in my parenthesis above before it will execute. (i.e. define it as an output before it will display and execute properly?)

 

[Maven4Champ]

Ok Guys - I have an update.

My procedure parses correctly and saves with no errors in TOAD.

CREATE OR REPLACE PROCEDURE SP_C2C_ROLLUP
(CR_CURSOR IN OUT SYS_REFCURSOR,
StartDate IN DATE,
EndDate IN DATE)
AS
DEALER_CODE_ROLLUP VARCHAR(153);
REGION				VARCHAR(25);
TTCM_DLR_AVG			 NUMBER;
TTCM_CREDIT				 NUMBER;
TTCM_REHASH				 NUMBER;
TTCM_CREDIT_TO_GEN		 NUMBER;
TTCM_GEN_TO_RECVD		 NUMBER;
TTCM_RECVD_T0_FUND		 NUMBER;
QTY						 NUMBER;
BEGIN
OPEN CR_CURSOR FOR
SELECT (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME) DEALER_CODE_ROLLUP,
		V_LIFE_OF_A_DEAL.REGION, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_DEAL) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_DLR_AVG, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_REHASH) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_REHASH, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_CREDIT_TO_GEN) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_CREDIT_TO_GEN, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_GEN_TO_RECVD) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_GEN_TO_RECVD, 
       (SUM(V_LIFE_OF_A_DEAL.TTCM_RECVD_TO_FUNDED) / COUNT(V_LIFE_OF_A_DEAL.APP_NUM)) AS TTCM_RECVD_T0_FUND, 
       COUNT(V_LIFE_OF_A_DEAL.APP_NUM) AS QTY 
  FROM SIEBEL.V_LIFE_OF_A_DEAL 
 WHERE TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) >= 'StartDate' AND TO_DATE(V_LIFE_OF_A_DEAL.FUND_DATE) <= 'EndDate'
 GROUP BY (V_LIFE_OF_A_DEAL.DEALER_CODE || ' - ' || V_LIFE_OF_A_DEAL.DEALER_NAME),
			V_LIFE_OF_A_DEAL.REGION;
CLOSE CR_CURSOR;
END SP_C2C_ROLLUP;
/

My problem is, how do I now execute this with the appropriate parameters of date ranges to test to verify the results? Also can anyone review the code and let me know of any issues I might face?