DYNAMIC SQL SELECTION QUESTION

[ITAPORT06]

I posted a question earlier but that did not help me.

I need to insert data from joined files A - B into C

The tables are joined by the fields: SUN_DB & ACCOUNT_NR

BUT!!!!

The name of file B's is partial "variable" and comes out of file A
field: SUN_DB

Example:
Row of File A: field SUN_DB = "ZZZ" so File A has to be joined
with Table: "B_ZZZ" by the fields SUN_DB & ACCOUNT_NR

Row of File A: field SUN_DB= "YYY" so File A has to be joined
with Table: "B_YYY" by the fields SUN_DB & ACCOUNT_NR

There are +- 700 SUN_DB codes and so 700 TABLES TO JOIN WITH
IN SHORT I need to run +-700 joines between Table A and variable Table
Name B named: SALFLDG'||SUN_DB||'

Do I make myself clear?

I use the following script but some say it is far too complex for I
try to achieve. For me this script is working fine but it is ignoring
the following conditions "AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
'ABNA%'; "
Can anyone explain me why?

DECLARE retval INTEGER; CURSOR obj_cur IS

SELECT DISTINCT 'INSERT INTO C_SPIN (SUN_DB, ACCNT_CODE, TRANS_DATE,
AMOUNT, ANAL_A0, ANAL_T0)

SELECT SSRFACC.SUN_DB,SRFACC.ACCNT_CODE,TRANS_DATE,
AMOUNT,SSRFACC.ANAL_A0, ANAL_T0 FROM SSRFACC,SALFLDG'||SUN_DB||'

WHERE SSRFACC.ACCNT_CODE = SALFLDG'||SUN_DB||'.ACCNT_CODE AND
SSRFACC.SUN_DB ='''||SUN_DB||''' ' CMDSQL
FROM SSRFACC

WHERE SUN_DB LIKE 'B%' AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
'ABNA%';

drop_cursor INTEGER;
out_str VARCHAR2(1000);
BEGIN FOR obj_rec IN obj_cur LOOP drop_cursor :=
DBMS_SQL.OPEN_CURSOR;
out_str := obj_rec.cmdsql;
DBMS_SQL.PARSE (drop_cursor, out_str,
DBMS_SQL.NATIVE);
retval := dbms_sql.EXECUTE(drop_cursor);
DBMS_SQL.CLOSE_CURSOR (drop_cursor);
END LOOP;
END;

[Lothar Armbruester]

ITAPORT06 wrote:
[...]

>
> Example:
> Row of File A: field SUN_DB = "ZZZ" so File A has to be joined
> with Table: "B_ZZZ" by the fields SUN_DB & ACCOUNT_NR
>
> Row of File A: field SUN_DB= "YYY" so File A has to be joined
> with Table: "B_YYY" by the fields SUN_DB & ACCOUNT_NR
>
> There are +- 700 SUN_DB codes and so 700 TABLES TO JOIN WITH
> IN SHORT I need to run +-700 joines between Table A and variable Table
> Name B named: SALFLDG'||SUN_DB||'
>

Hello ITAPORT06,
maybe it's worth to rethink your database design. What about putting all
700 tables into one adding a column sun_db so that you can join on this
column? This would eliminate the need for dynamic SQL altogether.

> Do I make myself clear?
>
> I use the following script but some say it is far too complex for I
> try to achieve. For me this script is working fine but it is ignoring
> the following conditions "AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
> 'ABNA%'; "
> Can anyone explain me why?
>
> DECLARE retval INTEGER; CURSOR obj_cur IS
>
> SELECT DISTINCT 'INSERT INTO C_SPIN (SUN_DB, ACCNT_CODE, TRANS_DATE,
> AMOUNT, ANAL_A0, ANAL_T0)
>
> SELECT SSRFACC.SUN_DB,SRFACC.ACCNT_CODE,TRANS_DATE,
> AMOUNT,SSRFACC.ANAL_A0, ANAL_T0 FROM SSRFACC,SALFLDG'||SUN_DB||'
>
> WHERE SSRFACC.ACCNT_CODE = SALFLDG'||SUN_DB||'.ACCNT_CODE AND
> SSRFACC.SUN_DB ='''||SUN_DB||''' ' CMDSQL
> FROM SSRFACC
>
> WHERE SUN_DB LIKE 'B%' AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
> 'ABNA%';
>
> drop_cursor INTEGER;
> out_str VARCHAR2(1000);
> BEGIN FOR obj_rec IN obj_cur LOOP drop_cursor :=
> DBMS_SQL.OPEN_CURSOR;
> out_str := obj_rec.cmdsql;
> DBMS_SQL.PARSE (drop_cursor, out_str,
> DBMS_SQL.NATIVE);
> retval := dbms_sql.EXECUTE(drop_cursor);
> DBMS_SQL.CLOSE_CURSOR (drop_cursor);
> END LOOP;
> END;
>

I don't see why the where condition is not executed but there is
something that you can improve:
I would put the dbms_sql.open_cursor and dbms_sql.close_cursor out of
the loop. A SQL cursor can be used for more than one SQL. You can reuse
it just by parsing a new SQL.
Maybe it would help to output the SQLs using dbms_output to see what's
wrong.
Eventually, you might have a look at the execute immediate feature of
Oracle 8i and up. This is a bit easier to handle and according to the
docs it is more performant.

Hope that helps,
Lothar
--
Lothar Armbrüster | [email]la@oktagramm.de[/email]
Hauptstr. 26 | [email]la@heptagramm.de[/email]
D-65346 Eltville | [email]lothar.armbruester@t-online.de[/email]

[ITAPORT06]

Lothar & Paul thank your for yor contribution but my prblem is still
not solved.

Paul: Yes the application I want to extract data from is Systems
Union Sun. So you are aware of the database structure. The dynamic sql
statement underneath works fine BUT I only have one problem is the
statement almost at the end of the script:

>WHERE SUN_DB LIKE 'B%' AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
>'ABNA%';

It does NOT ignore SUN_DB Like B% BUT is does ignore ACCNT_TYPE <>
'P' AND ANAL_A0 LIKE 'ABNA%'

It does not select records on the last 2 argument
Why is that and how can Isolve this?
Thanks



On Tue, 01 Jul 2003 23:06:24 +0200, ITAPORT06 <mokat67@hotmail.com>
wrote:

>I posted a question earlier but that did not help me.
>
>I need to insert data from joined files A - B into C
>
>The tables are joined by the fields: SUN_DB & ACCOUNT_NR
>
>BUT!!!!
>
>The name of file B's is partial "variable" and comes out of file A
>field: SUN_DB
>
>Example:
>Row of File A: field SUN_DB = "ZZZ" so File A has to be joined
>with Table: "B_ZZZ" by the fields SUN_DB & ACCOUNT_NR
>
>Row of File A: field SUN_DB= "YYY" so File A has to be joined
>with Table: "B_YYY" by the fields SUN_DB & ACCOUNT_NR
>
>There are +- 700 SUN_DB codes and so 700 TABLES TO JOIN WITH
>IN SHORT I need to run +-700 joines between Table A and variable Table
>Name B named: SALFLDG'||SUN_DB||'
>
>Do I make myself clear?
>
>I use the following script but some say it is far too complex for I
>try to achieve. For me this script is working fine but it is ignoring
>the following conditions "AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
>'ABNA%'; "
>Can anyone explain me why?
>
>DECLARE retval INTEGER; CURSOR obj_cur IS
>
>SELECT DISTINCT 'INSERT INTO C_SPIN (SUN_DB, ACCNT_CODE, TRANS_DATE,
>AMOUNT, ANAL_A0, ANAL_T0)
>
>SELECT SSRFACC.SUN_DB,SRFACC.ACCNT_CODE,TRANS_DATE,
>AMOUNT,SSRFACC.ANAL_A0, ANAL_T0 FROM SSRFACC,SALFLDG'||SUN_DB||'
>
>WHERE SSRFACC.ACCNT_CODE = SALFLDG'||SUN_DB||'.ACCNT_CODE AND
>SSRFACC.SUN_DB ='''||SUN_DB||''' ' CMDSQL
>FROM SSRFACC
>

> >>>>>>>>>>>>>>>>>>>>>>>>>>WHERE SUN_DB LIKE 'B%' AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'ABNA%';

>
>drop_cursor INTEGER;
>out_str VARCHAR2(1000);
>BEGIN FOR obj_rec IN obj_cur LOOP drop_cursor :=
>DBMS_SQL.OPEN_CURSOR;
>out_str := obj_rec.cmdsql;
>DBMS_SQL.PARSE (drop_cursor, out_str,
>DBMS_SQL.NATIVE);
>retval := dbms_sql.EXECUTE(drop_cursor);
>DBMS_SQL.CLOSE_CURSOR (drop_cursor);
>END LOOP;
>END;
>

[Paul Brewer]

"ITAPORT06" <mokat67@hotmail.com> wrote in message
news:m37ggv4qq86sg5q3v970bhjet1ran14ui7@4ax.com...

> Lothar & Paul thank your for yor contribution but my prblem is still
> not solved.
>
> Paul: Yes the application I want to extract data from is Systems
> Union Sun. So you are aware of the database structure. The dynamic sql
> statement underneath works fine BUT I only have one problem is the
> statement almost at the end of the script:
>

Sorry, I didn't really check the original SQL; I responded to the
'Redesign!' comment.
As to the original problem, is it an issue with nulls?

Regards,
Paul