Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help
SQL query help [message #210492] Thu, 21 December 2006 01:44 Go to next message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi guru's,

i am new to SQL side, i have a urgent requirement joining of two tables.

table1(wip_doc) column's are

doc_num
create_date
doc_count


table2(wip_move) column's are

doc_num
item_attribute
item_value
item_count


but my select statment like the below

select wd.doc_num,wd.create_date,wd.doc_count,wm.item_attribute,wm.item_value,wm.item_count from wip_doc wd, wip_move wm where wd.doc_num = wm.doc_num ORDER BY wd.doc_num

with some extran AND condition..

those conditon only i need from you people.

i mean need to select one more column i.e is difference_count,

this column value is not in the above two tables, but based upon the doc_num column of wip_doc table, the item_value of wip_move table column need to sum(records) and then the difference_count column = (doc_count - sum(item_value))

then all the way final select command must be the same above select command with difference_count column with those sum(item_value) <> doc_count only need to display.



so example:

doc_num create_date doc_count item_attribute item_value item_count difference_count

docNum1 21/12/2006 300 PART_NUM 1003456 200
docNum1 21/12/2006 300 PART_NUM 1004789 100
docNum1 21/12/2006 300 PART_NUM 1004789 200
-------
total 500
-200
can anyone pls give me the select statements for the above idea.

with regards,
anbaz.






[Updated on: Thu, 21 December 2006 01:46]

Report message to a moderator

Re: SQL query help [message #210513 is a reply to message #210492] Thu, 21 December 2006 03:15 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
This could be one way of doing it (Not tested though...).



select a.doc_num,a.create_date,a.doc_count,b.item_attribute,b.item_value,b.item_count,a.diff from 
(select wd.doc_num,wd.create_date,wd.doc_count,item_tot_value,wd.doc_count - item_tot_value diff from wip_doc wd, (select doc_num ,sum(item_value) item_tot_value from wip_move group by doc_num) wp where wd.doc_num = wp.doc_num and item_tot_value <> wd.doc_count) a, wip_move b where 
a.doc_num = b.doc_num ORDER BY a.doc_num 
/


Re: SQL query help [message #210704 is a reply to message #210513] Thu, 21 December 2006 20:28 Go to previous message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi Bonker,

Thanks, it works for me.

thank you very much.

with regards,
anbaz.
Previous Topic: materialized views log creation
Next Topic: about substitution parameter
Goto Forum:
  


Current Time: Mon Dec 05 02:53:20 CST 2016

Total time taken to generate the page: 0.08008 seconds