Home » SQL & PL/SQL » SQL & PL/SQL » getting row from both transaction tables (oracle 10g. linux os)
getting row from both transaction tables [message #385520] Tue, 10 February 2009 00:17 Go to next message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Hi All,

we have one small problem, we cant able to get the rows from both the transaction tables one is stock table(DM_LOCN_STOCK) and another is sales transactiom table (OS_CONS_DETAIL_DATE) and i have item master table (om_item), i want get all the rows from stock table with item detail and qty availabe and from the sales transaction table i want get which are item sold and there qty in given period, i have written a query with outer join but it is not displying all the rows, it showin only matching rows.
here is the my query


SELECT item_anly_code_01,
       item_anly_code_06,
       item_anly_code_02||item_anly_code_03||item_anly_code_04 item,
       sum(decode(lcs_locn_code,001,
                 (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issd_qty_bu,0))) THRM,
       sum(decode(CD_locn_code,001,(cd_qty))) HRM,
       sum(decode(lcs_locn_code,002,
                  (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issd_qty_bu,0))) TSAB,
       sum(decode(CD_locn_code,002,(cd_qty))) SAB,
       sum(decode(lcs_locn_code,003,
                  (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issD_qty_bu,0))) TCLK,
       sum(decode(CD_locn_code,003,(cd_qty))) CLK,
       sum(decode(lcs_locn_code,004,
                  (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issD_qty_bu,0))) TALK,
       sum(decode(CD_locn_code,004,(cd_qty))) ALK,
       sum(decode(lcs_locn_code,005,
                  (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issD_qty_bu,0))) TAHL,
FROM  DM_LOCN_STOCK, 
      OM_ITEM,
      OS_CONS_DETAIL_DATE
WHERE lcs_item_code=item_code
  AND cd_item_code=item_code
  AND lcs_item_code=cd_item_code(+)
  AND cd_txn_type='INV'
  AND (nvl(lcs_stk_qty_bu,0) + nvl(lcs_rcvd_qty_bu,0)) - nvl(lcs_issD_qty_bu,0)>0
  and item_anly_code_01='IC009'
GROUP BY item_anly_code_01,item_anly_code_06,item_anly_code_02||item_anly_code_03||item_anly_code_04
ORDER BY item_anly_code_01,item_anly_code_06,item_anly_code_02||item_anly_code_03||item_anly_code_04


please can any one help on this

Thanking you

[Formatted by Raj]

[Updated on: Tue, 10 February 2009 01:52] by Moderator

Report message to a moderator

Re: getting row from both transaction tables [message #385529 is a reply to message #385520] Tue, 10 February 2009 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't take the time to format the query as stated in OraFAQ Forum Guide, I didn't read it.

Regards
Michel
Re: getting row from both transaction tables [message #385542 is a reply to message #385529] Tue, 10 February 2009 01:41 Go to previous messageGo to next message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Hi Michel

Sorry i am new to this site, i am attaching the formated query, i hope this will give you clear format



Thanking You,
  • Attachment: testqery.txt
    (Size: 1.88KB, Downloaded 166 times)
Re: getting row from both transaction tables [message #385545 is a reply to message #385520] Tue, 10 February 2009 01:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ktcit wrote on Tue, 10 February 2009 07:17
we cant able to get the rows from both the transaction tables one is stock table(DM_LOCN_STOCK) and another is sales transactiom table (OS_CONS_DETAIL_DATE) and i have item master table (om_item), i want get all the rows from stock table with item detail and qty availabe and from the sales transaction table i want get which are item sold and there qty in given period, i have written a query with outer join but it is not displying all the rows, it showin only matching rows.

When using Oracle outer join operator, it is maybe good to read about it in the documentation. It is available e.g. online on http://tahiti.oracle.com/.
SQL Reference
Chapter 9 SQL Queries and Subqueries
Joins
Outer Joins
Quote:
If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

As you did not post, which column belongs to which table, it is on you to append the (+) operator to the ones belonging to the outer joined table.

But I see here one problem - as you outer join two (detail) tables, you will get all combinations of their rows for one (master) row. You should precise, how the details shall be presented. E.g. what shall be the resultset, when you have two rows in DM_LOCN_STOCK and three rows in OS_CONS_DETAIL_DATE for one row in om_item?

[Edit: just added the final question mark]

[Updated on: Tue, 10 February 2009 01:48]

Report message to a moderator

Re: getting row from both transaction tables [message #385552 is a reply to message #385545] Tue, 10 February 2009 02:22 Go to previous messageGo to next message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Thanks for your reply

i am attaching new query script which is indicating which columns belongs to which table.

we need the out put as follows
CLKSTARCORALRED item code from item master

ITEM THRM HRM TSAB SAB
IC009 L CLK CLKSTARCORALRED 4 1 0 1

THRM is from stock table(dm_locn_stock) and HRM is from sales table(os_cons_detail_date)

Thanking You

Re: getting row from both transaction tables [message #385566 is a reply to message #385520] Tue, 10 February 2009 02:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
What do you not understand on
Quote:
you must use the (+) operator in all of these conditions
, as your query still contains one outer join operator. Are you also aware, that the join condition with (+) is totally useless there, as it does not add anything to the previous two ones?
By the way, it is year 2009, it is easier to use ANSI join syntax instead (as also recommended by Oracle in the link I posted).

To the query, you cannot do it in one GROUP BY because of multiplication. Just do it like this (if ITEM_CODE is unique in the OM_ITEM table):
with a1 as (select a.lcs_item_code, <sum columns from a>
           from a
           where <where conditions on a>
           group by a.lcs_item_code),
     c1 as (select c.cd_item_code, <sum columns from c>
           from c
           where <where conditions on c>
           group by c.cd_item_code)
select <columns from b>, <sums from a and c>
from b left join c1 on (b.item_code = c1.cd_item_code)
       left join a1 on (b.item_code = a1.lcs_item_code)
order by <columns from b>


[Edit: typo in the query]

[Updated on: Tue, 10 February 2009 02:43]

Report message to a moderator

Re: getting row from both transaction tables [message #385614 is a reply to message #385566] Tue, 10 February 2009 04:46 Go to previous messageGo to next message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Dear Flyboy,
Thanks for your help, according to your post i have formated my query, while running the query i am getting error saying ora-24374: define not done before tetch or execute and fetch. i am attaching my formated query for your reference.
Plese help me on this

Thanking You
  • Attachment: test123.sql
    (Size: 1.64KB, Downloaded 155 times)
Re: getting row from both transaction tables [message #385618 is a reply to message #385520] Tue, 10 February 2009 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:

ORA-24374: define not done before fetch or execute and fetch
Cause: The application did not define output variables for data being fetched before issuing a fetch call or invoking a fetch by specifying a non-zero row count in an execute call.
Action: Issue OCI define calls for the columns to be fetched.



That says to me that you haven't told oracle what you want it to put the results of the query into.
In other words it's not a problem with the actual select statement and posting it isn't really going to help
Re: getting row from both transaction tables [message #385621 is a reply to message #385614] Tue, 10 February 2009 05:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ktcit wrote on Tue, 10 February 2009 11:46
while running the query i am getting error saying ora-24374: define not done before tetch or execute and fetch. i am attaching my formated query for your reference.

You forgot to post the most important thing: how are you "running the query"?
I am curious whether you were successful with the previous query. Anyway, it is calling issue; maybe does not like the WITH clause, maybe ... (but it is hard to tell without knowing exactly the tool you use for executing it).
Re: getting row from both transaction tables [message #385623 is a reply to message #385618] Tue, 10 February 2009 05:16 Go to previous messageGo to next message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Hi,

We want get the all rows from table a (table a is stock table), and get the row from c table which is sales from purticular period, b is my item_master where we have uniq item code. when i am running the query its giving wrong, i am ataching sample out put for reference
ITEM_ANLY_CODE_01,ITEM_ANLY_CODE_06,ITEM,THRM,HRM,TSAB,SAB,TCLK,CLK,TALK,ALK,TAHL
IC009,BA,CLKMACHENDERSONPNK,,,,,4,2,,,
IC009,BA,CLKTIAFREESIAEBN,,,,,8,1,,,
IC009,BA,CLKTIGERBAMBOOBLK,,,,,8,,,,
IC009,BA,CLKTINYHENDRSNBLK,,,,,40,2,,,
IC009,BA,CLKTRUEBEAUTYPBLK,,,,,5,,,,
IC009,G,CLKDINOBARBLK,8,,,,19,30,7,,9
Re: getting row from both transaction tables [message #385624 is a reply to message #385621] Tue, 10 February 2009 05:18 Go to previous message
ktcit
Messages: 24
Registered: August 2006
Location: OMAN
Junior Member

Hi,
I am running in the toad version 9.5

Thanks lot
Previous Topic: Error while using ANYDATA through VARRAY
Next Topic: ON DELETE CASCADE and ON UPDATE CASCADE
Goto Forum:
  


Current Time: Sun Dec 04 17:00:55 CST 2016

Total time taken to generate the page: 0.07500 seconds