Re: DYNAMIC SQL SELECTION QUESTION

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_at_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;
>
Received on Sun Jul 06 2003 - 07:56:27 CDT