Need to create dynamic sql in SP for crystal report

[AurobindoSaha]

I am trying to create a dynamic sql query in SP that I like to use in crystal report.

The query has two parts:
1. A static part -----STORED IN STR_1
select .....
from .....
where ... = ...
and ... = ...
2. A dynamic part ---------STORED IN STR_2
if (.....) then
"and ABC = XYZ"
elsif (...) then
"and QWE = RTY"
end if

Now I like to concatenate str1 and str2 into a new variable str3

str3 = str1 + str2;

Then I like to execute str3.
I need help to write this SP in Oracle 8i?
I am sending the actual code below:

CREATE OR REPLACE procedure CRYSTAL_NECLLI_SP
(
SUB_Cursor OUT,
CRYSTAL_NECLLI_PKG.NECLLIType,
p_NECLLI in varchar2(11),
p_limitation in varchar2(4),
p_service_category in varchar2(5),
p_CNUM_status in varchar2(20)
)
as

declare

v_str1 varchar2(4000) := "";
v_str2 varchar2(1000) := "";
v_str3 varchar2(2) := "";

lv_NECLLI varchar2(11);
lv_limitation varchar2(4);
lv_service_category varchar2(5);
lv_CNUM_status varchar2(20);

begin

lv_NECLLI := UPPER(p_NECLLI);
lv_limitation := UPPER(p_limitation);
lv_service_category := UPPER(p_service_category);
lv_CNUM_status := UPPER(p_CNUM_status);


v_str1 ="open SUB_Cursor
SELECT
CNUM_TN_Y.NPA,
CNUM_TN_Y.NXX,
CNUM_TN_Y.LINE,
CNUM_TN_Y.SVC_CAT_CD,
CNUM_TN_Y.EXCN_CD,
CNUM_TN_Y.BGN_DAT,
CNUM_TN_Y.END_DAT,
CNUM_TN_Y.NEID,
CNUM_TN_Y.CUSTID,
CNUM_TN_Y.CUID_CD,
ODI_SRC_SYS_STATUS_Y.SYS_ID,
ODI_SRC_SYS_STATUS_Y.LOAD_ID,
ODI_SRC_SYS_STATUS_Y.STAT_DESCR
FROM
CNUM_TN_Y CNUM_TN_Y,
ODI_SRC_SYS_STATUS_Y ODI_SRC_SYS_STATUS_Y

WHERE
CNUM_TN_Y.CNUM_STAT_CD = ODI_SRC_SYS_STATUS_Y.SYS_STAT_CD(+) AND
CNUM_TN_Y.NEID = lv_NECLLI and
CNUM_TN_Y.END_DAT >= TO_DATE ('31-12-2037 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
CNUM_TN_Y.END_DAT <= TO_DATE ('31-12-2037 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
ODI_SRC_SYS_STATUS_Y.SYS_ID = 1 AND
ODI_SRC_SYS_STATUS_Y.LOAD_ID = 1
";

if (lv_service_category = "--ALL--") then
if (lv_limitation = "--ALL--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.NEID = lv_NECLLI;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status;";
else if(lv_limitation = "--NONE--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "And CNUM_TN_Y.EXCN_CD is null"
else
v_str2= "ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD is null;";
else
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.EXCN_CD = lv_limitation;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD = lv_limitation"
else
if (lv_limitation = "--ALL--") then
if(lv_CNUM_status)="--ALL--") then
v_str2 = "AND CNUM_TN_Y.NEID = lv_NECLLI and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else if(lv_limitation = "--NONE--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.EXCN_CD is null and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD is null and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
if(lv_CNUM_status = "--ALL--") then
v_str2 = " CNUM_TN_Y.EXCN_CD = lv_limitation and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD = lv_limitation and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";




v_str3 = v_str1 + v_str2 + ";"
dbms_output str3;
--dbms_sql.execute (str3) ??Is this correct
end CRYSTAL_NECLLI_SP;
/

 

[synapsevampire]

As with most questions, this would be dependent upon the Crystal version and the database being used. At bare minimum, that should always accompany technical requests.

You're probably better served to post this question in the appropriate database forum as not too many people here are SQL Programmers.

I would guess that you're using Oracle, and if you are, then you should read the whitepaper on SP's and Crystal as there are strict requirements, and make sure that you use the native connectivity or the CR supplied ODBC driver if using CR 8.5 or below:

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

-k

 

[AurobindoSaha]

Hi synapsevampire
We are using crystal reports 8.5 and oracle 8i. I already looked in the paper that you sent. I mentions nothing about dynamic sql.
Since I require this in report to be implemented I posted this in Crystal Reports forum. Anyway I am also posting the same in Oracle forum
Thanks
Aurobindo

 

[synapsevampire]

I don't know of anything that prevents you from using dynamic SQL, except perhaps connectivity.

Are you having a specific problem using this?

Do you know what type of connectivity that you're using?

Did you follow the guidelines in terms of Packages, etc.?

-k

 

[AurobindoSaha]

Yes I am following the guidelines in terms of packages, ect. I cannot complile the SP in oracle even. I tried to compile this with toad, but cold not