ORA-22818 subquery expression not allowed here 1

[Stevennn]

It's not reading CASE in GROUP BY! Can anyone explain what is wrong?

This script works only if I delet SUM and GROUP BY or if i delet CASE statment from GROUP BY than it works too (but than it freezes after 100 records, i think because it hits SO transactions)

Thank-you!!!!

SELECT b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type
,CASE
WHEN a.gl_tr_type = 'SO' THEN
(SELECT g. cust_name
FROM F_ACCT_TRX_HIST_STG2 a
,finmart.D_CUSTOMER g
,dssmart.f_sales_invoice h
WHERE a.gl_doc = h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr)
ELSE
NULL
END cust_name
,SUM(a.gl_amt)
FROM F_ACCT_TRX_HIST_STG2 a
,D_ENTITY_STG2 b
,D_COSTCTR_STG2 c
,D_ACCTS_STG2 d
,D_SUBACCTS_STG2 e
,D_PERIOD_STG1 f
WHERE a.gl_ent = b.en_ent
AND c.cc_cstctr = UPPER(a.gl_cc)
AND d.acct_acc = a.gl_acc
AND e.sa_sub = a.gl_sa
AND a.gl_eff_dt = f.calendar_date
GROUP BY b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
, CASE
WHEN a.gl_tr_type = 'SO' THEN
(SELECT g. cust_name
FROM F_ACCT_TRX_HIST_STG2 a
,finmart.D_CUSTOMER g
,dssmart.f_sales_invoice h
WHERE a.gl_doc = h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr)
ELSE
NULL
END
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type

 

[SantaMufasa]

Steven,

There are some things that puzzle me about your query:

First, the sub-query in your CASE statement seems to be completely self-contained and does not correlate to anything outside of that sub-query.

If that is the case, then, as a test, I would like you to run that sub-query as a standalone COUNT(*) query and post the results here. So, please run this code:

SELECT count(*)
           FROM    F_ACCT_TRX_HIST_STG2   a
                 ,finmart.D_CUSTOMER      g
                 ,dssmart.f_sales_invoice h
           WHERE  a.gl_doc = h.inv_nbr
           AND    h.inv_cust_bill_to_nbr = g.cust_nbr;

If the result is more than 1, then you have a logic error since the CASE statement can output only one value and your query returns more than one result.

If, however, the result is only 1, then you should be able to revise your query to read as follows (with my changes in bold font):

SELECT b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,[b]decode(type,'SO',g.cust_name) cust_name[/b]
      ,SUM(a.gl_amt)
FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f[b]
      ,(SELECT g. cust_name
              FROM   F_ACCT_TRX_HIST_STG2    a
                    ,finmart.D_CUSTOMER      g
                    ,dssmart.f_sales_invoice h
              WHERE  a.gl_doc = h.inv_nbr
              AND    h.inv_cust_bill_to_nbr = g.cust_nbr) g[/b]
WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date 
GROUP  BY b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,[b]decode(type,'SO',g.cust_name)[/b]
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

Hello and thank-you for your HELP!
I did ran it as you asked and i get count of 125626! But than again the way you asked me to ran the query is without gl_tr_type = 'SO', that script should be only executed WHEN gl_tr_type = 'SO'. I ran the record count where i added gl_tr_type = 'SO' and i got same count 125626!

I will post my original query (in bold) first and than the IF query and try to explain in detail what I'm trying to accomplish.

SELECT b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type
,SUM(a.gl_amt)

FROM F_ACCT_TRX_HIST_STG2 a
,D_ENTITY_STG2 b
,D_COSTCTR_STG2 c
,D_ACCTS_STG2 d
,D_SUBACCTS_STG2 e
,D_PERIOD_STG1 f

WHERE a.gl_ent = b.en_ent
AND c.cc_cstctr = UPPER(a.gl_cc)
AND d.acct_acc = a.gl_acc
AND e.sa_sub = a.gl_sa
AND a.gl_eff_dt = f.calendar_date

GROUP BY b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type

Now i have thisIF query)
SELECT g.cust_name

FROM F_ACCT_TRX_HIST_STG2 a
,finmart.D_CUSTOMER g
,dssmart.f_sales_invoice h
WHERE a.gl_tr_type = 'SO'
AND a.gl_doc = h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr;



I will have 2 more WHERE gl_tr_type = 'DA' or 'FA' but with different Table Joins (dont have the busn rule yet) therefore i was thinking of doing nested CASE statment.

I will have 5 more different types of gl_tr_type which will not have anything in cust_name, but i still need to see those transactions where it will not be equal to SO, DA or FA. I understand that the cust_name should be blank if it's not SO, DA or FA, in those blank firld i just want to see N/A.

Thank-you so much for your HELP!!!

 

[Stevennn]

hello Mufasa,
Did i explain clearly enough?


thank-you

 

[SantaMufasa]

Steven, I'm sorry for my not responding earlier, but given the information you have provided, I do not understand your need enough to suggest a solution. Please simplify your original query to something like:

SELECT SUM(<expression>), <CASE or DECODE expression>
FROM <one table>
WHERE <minimal condition>
GROUP BY <CASE or DECODE expression>;

The best simplification is one that has minimal code, but still throws an error. Please also post "CREATE TABLE..." and "INSERT INTO..." statements upon which your code depends.

If you can do the above, then we can devise a solution that resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

let me please try one more time with explanation of what i need...........

I will repost my query which was posted previously in bold but please look at my (WHERE clause and FROM) the lines in bold and italic will show the change.

Now with this statement i will get only those transactions that will be gl_tr_type = 'SO' ONLY

How can i return those transactions that are gl_tr_type = 'SO' or 'FA*' AND those transactions that will NOT be EQUAL to SO or 'FA*' will have N/A in "g.cust_name column"

*but later i will have another rule to ADD
"(a.gl_tr_type= 'FA' AND a.gl_doc = tt.dnr AND tt.invoice = g.cust_nbr )"

Thank-you so MUCH!!!!!!!!!!!!


SELECT b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,g. cust_name
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type
,SUM(a.gl_amt)

FROM F_ACCT_TRX_HIST_STG2 a
,D_ENTITY_STG2 b
,D_COSTCTR_STG2 c
,D_ACCTS_STG2 d
,D_SUBACCTS_STG2 e
,D_PERIOD_STG1 f
,finmart.D_CUSTOMER g
,dssmart.f_sales_invoice h

WHERE a.gl_ent = b.en_ent
AND c.cc_cstctr = UPPER(a.gl_cc)
AND d.acct_acc = a.gl_acc
AND e.sa_sub = a.gl_sa
AND a.gl_eff_dt = f.calendar_date
AND (a.gl_tr_type = 'SO
AND a.gl_doc = h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr)

GROUP BY b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,g. cust_name
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type

 

[SantaMufasa]

Again, without some sample tables and data, I can only make untested suggestions. For your specifications, above, here are my code-adjustment suggestions:

SELECT b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng[b]
      ,nvl(g.cust_name,'N/A')[/b]
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,SUM(a.gl_amt)

FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f
      ,finmart.D_CUSTOMER      g
      ,dssmart.f_sales_invoice h 

WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date 
AND    (a.gl_tr_type = 'SO
AND    a.gl_doc = h.inv_nbr
AND    h.inv_cust_bill_to_nbr = g.cust_nbr[b](+)[/b])

GROUP  BY b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,[b]nvl(g.cust_name,'N/A')[/b]
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type

In the above code, the "NVL" function specifies that if "g.cust_name" is NULL, then return "N/A" instead of NULL. The "(+)" symbol specifies that for rows where "h.inv_cust_bill_to_nbr" matches no value in "g.cust_nbr", then return a row anyway, but with NULL as the content of any "g." columns.

I hope this helps. Let us know your findings from these adjustments.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

So this is my final script i checked and it works but it's just taking to much time.

What should i write in order whats in BOLD to be exucuted ONLY if gl_tr_type = 'SO',,,,,,,,,,,CASE????
Because how i understand is that those JOINs are going thru eventhough the gl_tr_tpe not equals to 'SO' therefore it's taking too loong!
THANK YOU FOR HELPNG!!!!!!!!

SELECT b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,DECODE(gl_tr_type,'SO','"'||trim(g.cust_name )||'"', '"CHA-NONE"') cust_name
,f. fiscal_month
,f. fiscal_year_lng
,SUM(a.gl_amt)
,d. acct_type
,a. gl_tr_type


FROM F_ACCT_TRX_HIST_STG2 a
,D_ENTITY_STG2 b
,D_COSTCTR_STG2 c
,D_ACCTS_STG2 d
,D_SUBACCTS_STG2 e
,D_PERIOD_STG1 f
,FINMART.D_CUSTOMER g
,dssmart.f_sales_invoice h

WHERE a.gl_ent = b.en_ent
AND c.cc_cstctr = UPPER(a.gl_cc)
AND d.acct_acc = a.gl_acc
AND e.sa_sub = a.gl_sa
AND a.gl_eff_dt = f.calendar_date
AND a.gl_doc = h.inv_nbr (+)
AND h.inv_cust_bill_to_nbr = g.cust_nbr(+)

GROUP BY b.en_ent
,e.sa_sub
,c.cc_cstctr
,d.acct_acc
,b. en_entity_lng
,e. sa_sub_lng
,c. cost_ctr_lng
,d. acct_acc_lng
,DECODE(gl_tr_type,'SO','"'||trim(g.cust_name )||'"','"CHA-NONE"')
,f. fiscal_month
,f. fiscal_year_lng
,d. acct_type
,a. gl_tr_type

 

[SantaMufasa]

...therefore it's taking too loong!

How many seconds is it taking presently?

How many seconds should it take?
How many seconds was the fastest it has taken under different circumstances?

Do you have indexes on all columns that you use in your WHERE clauses?
What are the results for the following query:

SELECT table_name, last_analyzed
from user_tables
where table_name in
    ('F_ACCT_TRX_HIST_STG2'
    ,'D_ENTITY_STG2'
    ,'D_COSTCTR_STG2'
    ,'D_ACCTS_STG2'
    ,'D_SUBACCTS_STG2'
    ,'D_PERIOD_STG1')
union
select table_name, last_analyzed
from all_tables
where (owner = 'FINMART' and table_name ='D_CUSTOMER')
   or (owner = 'DSSMART' and table_name = 'F_SALES_INVOICE')
/

Please post your responses to these questions, and perhaps we can do some performance enhancement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

Hello,

I just simplified the queries!!!
How would you insert the 1st statement (and be read as IF or CASE) into the 2nd Script?



All the testing Im doing are all in TOAD!!!

1st
SELECT
g.cust_name

FROM
F_ACCT_TRX_HIST_STG2 a
,FINMART.D_CUSTOMER g
,dssmart.f_sales_invoice h
WHERE
(a.gl_tr_type = 'SO'
AND a.gl_doc = h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr)


2nd

SELECT b.en_ent
,e.sa_sub
,c.cc_cstctr
,SUM(a.gl_amt)

FROM F_ACCT_TRX_HIST_STG2 a
,D_ENTITY_STG2 b
,D_COSTCTR_STG2 c


WHERE a.gl_ent = b.en_ent
AND c.cc_cstctr = UPPER(a.gl_cc)
AND e.sa_sub = a.gl_sa


GROUP BY b.en_ent
,e.sa_sub
,c.cc_cstctr


As to answer the question regarding indexes: I did not set up any indexes yet!
Right now with statement it takes over couple of hours, it should only take under 40 mints.

I think my error is that I’m doing outer joins. If I ran my 1st script against my data it takes 5 minutes.

Last analyzed were done 2 weeks ago.

 

[SantaMufasa]

How would you insert the 1st statement (and be read as IF or CASE) into the 2nd Script?

I'm sure that we can combine the 1st query's code and result(s) into the 2nd script, but before we can do that, we need to understand clearly what data in the 2nd query should we use to correlate (and thus drive) the matching of data we should return from the 1st query.


If we do not correlate the data between the two queries, then an un-correlated subquery returns 125,626 rows...We want only 1 row to return. To make that happen, you must tell the subquery how to narrow down the 125,626 rows to just the 1 row (at most) that relates to each row in the outer (2nd) query.

So, Steven, the specific question for you to answer, so that we can combine the two queries is, "What data from each row of Query Two uniquely identifies a single row from the 125,626 rows of Query One?

Once you provide the uniquely identifying values from Query Two, we can incorporate Query One into Query Two.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

"What data from each row of Query Two uniquely identifies a single row from the 125,626 rows of Query One?

if gl_tr_type = 'SO' than i will pulla gl_doc which will have a unique code.......to join the h table.....

does this answer the question?

Thank-you



This doesnt answer also?

This script gives me the exact result set I need, (takes less under 1 minute.)

Now the problem that i have is that: I need to do the SUM for a.gl_amt....by doing the SUM than I need to do GROUP BY IF i do GROUP BY than the CASE is not READ.
it says "ORA-22818 subquery expression not allowed here"

How can i do write this script in order the SUM to work?

SELECT b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,CASE
         WHEN a.gl_tr_type = 'SO' THEN
          (SELECT g. cust_name
           FROM  
                  finmart.D_CUSTOMER      g
                 ,dssmart.f_sales_invoice h
           WHERE  a.gl_doc = h.inv_nbr
           AND    h.inv_cust_bill_to_nbr = g.cust_nbr)
         ELSE
          'N/A'
       END cust_name
      ,a.gl_amt
FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f
WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date

Thak-you very much for taking time to explain!!!!

 

[Stevennn]

Mufasa,
I tried to do inline view,,,,,,,,
well it works ONLY if i exclude x.cust_name as SOON as I include x.cust_name into SELECT than it's just freezes up and no result...........(using TOAD)

SELECT x. en_ent
      ,x. sa_sub
      ,x. cc_cstctr
      ,x. acct_acc
      ,x. en_entity_lng
      ,x. sa_sub_lng
      ,x. cost_ctr_lng
      ,x. acct_acc_lng
                        ,x. cust_name
      ,x. fiscal_month
      ,x. fiscal_year_lng
      ,x. acct_type
      ,SUM(x.gl_amt) 
FROM 
    (SELECT b.en_ent
      ,e. sa_sub
      ,c. cc_cstctr
      ,d. acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,CASE
         WHEN a.gl_tr_type = 'SO' THEN
          (SELECT g. cust_name
           FROM  
                  finmart.D_CUSTOMER      g
                 ,dssmart.f_sales_invoice h
           WHERE  a.gl_doc = h.inv_nbr
           AND    h.inv_cust_bill_to_nbr = g.cust_nbr)
         ELSE
          'N/A'
       END cust_name
      ,a.gl_amt
FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f
WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date) x
GROUP BY  x. en_ent
         ,x. sa_sub
         ,x. cc_cstctr
         ,x. acct_acc
         ,x. en_entity_lng
         ,x. sa_sub_lng
         ,x. cost_ctr_lng
         ,x. acct_acc_lng
	                   ,x. cust_name
         ,x. fiscal_month
         ,x. fiscal_year_lng
         ,x. acct_type

 

[Stevennn]

also everything works if I dont do SUM and then GROUP BY

 

[SantaMufasa]

Steven, I'm happy to help you produce code that works if you can please post minimal "CREATE TABLE..." and "INSERT INTO..." statements to reproduce your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

CREATE TABLE ACCT_HIST
(
  GL_ACC          VARCHAR2(10 BYTE),
  GL_ACC_DESC     VARCHAR2(17 BYTE),
  GL_SA           VARCHAR2(10 BYTE),
  GL_SA_DESC      VARCHAR2(17 BYTE),
  GL_CC           VARCHAR2(10 BYTE),
  GL_CC_DESC      VARCHAR2(17 BYTE),
  GL_ENT          VARCHAR2(10 BYTE),
  GL_ENT_DESC     VARCHAR2(17 BYTE),
  GL_AMT          NUMBER,
  GL_PERIOD       VARCHAR2(10 BYTE),
  GL_FISCAL_YEAR  VARCHAR2(10 BYTE),
  GL_TR_TYPE     VARCHAR2(2 BYTE),
  GL_ACC_TYPE     VARCHAR2(1 BYTE),
  CUST_NBR        VARCHAR2(12 BYTE),
  CUST_NAME       VARCHAR2(28 BYTE)
  CHANGED_DT      DATE,
  CREATED_DT      DATE,
)

INSERT INTO ACCT_HIST ( GL_ACC,
  GL_ACC_DESC,
  GL_SA,
  GL_SA_DESC ,
  GL_CC ,
  GL_CC_DESC ,
  GL_ENT  ,
  GL_ENT_DESC ,
  GL_AMT  ,
  GL_PERIOD     ,
  GL_FISCAL_YEAR  ,
  GL_TR_TYPE     ,
  GL_ACC_TYPE     ,
  CUST_NBR        ,
  CUST_NAME       ,
  CHANGED_DT      ,
  CREATED_DT      ,)
VALUES ('345',
 '"345"',
'876',
'"876"',
'N/A',
'"N/A"',
'3000',
'"3000"',
'"765.44"',
'"NOV"',
'"2005"',
'SO',
'A',
'"333"',
'"BMW"')

Does this Help, just a reminder I'm using TOAD for all the test not inserting yet!

thank-you!!!!!

 

[SantaMufasa]

Steven,

This may be a good start (if ACCT_HIST) matches one of these tables, below, that appear in your FROM clause:

...FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f

So, for me to devise code that works for you and closely matches your scenario, I need "CREATE TABLE..." and "INSERT INTO..." statements for all of the above tables.


Looking forward to your post.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Stevennn]

Mafusa,

Thankyou for taking your time to help me out with my problem!

I think i fixed it, I wont know untill I will validate the data.....

Again thank-you so much!!!