2

I have two tables ACTUAL AND ESTIMATE having unique column(sal_id, gal_id, amount, tax).

In ACTUAL table I have

actual_id, sal_id, gal_id, process_flag, amount, tax    
1          111     222     N             100     1  
2          110     223     N             200     2

In ESTIMATE table I have

estimate_id, sal_id, gal_id, process_flag, amount, tax      
3            111     222     N             50      1  
4            123     250     N             150     2 
5            212     312     Y             10      1

Now I want a final table, which should have record from ACTUAL table and if no record exist for sal_id+gal_id mapping in ACTUAL but exist in ESTIMATE, then populate estimate record (along with addition of amount and tax).

In FINAL table 

id  sal_id, gal_id, actual_id, estimate_id, total   
1   111     222     1          null         101 (since record exist in actual table for 111 222)   
2   110     223     2          null         202 (since record exist in actual table for 110 223)  
3   123     250     null       4            51  (since record not exist in actual table but estimate exist for 123 250)

(for 212 312 combination in estimate, since record already processed, no need to process again).

I am using Oracle 11g. Please help me on writing a logic in a single sql query?

Improve this question

2 Answers 2

Reset to default
2

Here is a SQLFiddle example

select sal_id,gal_id,actual_id,null estimate_id,amount,tax from actual where process_flag='N' 
union all
select sal_id,gal_id,null actual_id,estimate_id,amount,tax from estimate where process_flag='N' and
(sal_id,gal_id) not in (select sal_id,gal_id from actual where process_flag='N')
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Since I need use sequence number in my final table, If I use the above logic, getting this error. :( ORA-02287: sequence number not allowed here 02287. 00000 - "sequence number not allowed here" *Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate here in the statement. *Action: Remove the sequence number. Error at Line: 86 Column: 29
Try to use this query as a subquery for FROM class. select seq.nextval,t.* from (... above query ...) t;.Here is a good explanation
It is not working Valex. Getting ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined". I think this is due both ACTUAL & ESTIMATE tables having sal_id, gal_id column. Any other way of achieving it.
It has to work Here is an example. What query you are trying to run may be you added some column with the same name to final result query. Add your query to the question or here in comments.
0

Here is one way to do this, by taking everything from Actual and then just things in Estimate that aren't in Actual:

select a.*
from Actual a
union all
select e.*
from Estimate e
where not exists (select 1 from actual a where a.sal_id = e.sal_id and a.gal_id = e.gal_id) and
      e.process_flag = 'N'

This may not be the most efficient approach if you have a large amount of data. But it should work fine on smaller data sets.

Improve this answer

2 Comments

Since I need to use sequence number in my final table, If I use the above logic, getting this error. :( ORA-02287: sequence number not allowed here 02287. 00000 - "sequence number not allowed here" *Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate here in the statement. *Action: Remove the sequence number. Error at Line: 86 Column: 29
If you are inserting the results into a new table, be sure to include the list of columns in the insert statement (INSERT INTO T(<column list>). It sounds like you are trying to insert a value into into a sequence column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.