Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help
SQL Query Help [message #215305] Sat, 20 January 2007 17:46 Go to next message
sbaracho
Messages: 3
Registered: January 2007
Junior Member
Hello,

I have a table Z_AUDIT_ALL_ENTRIES which has the following fields:

BUKRS CHAR 4 Company Code
BELNR CHAR 10 Accounting Document Number
GJAHR NUMC 4 Fiscal Year
BLART CHAR 2 Document type
BUDAT DATS 8 Posting Date in the Document
CPUDT DATS 8 Accounting Document Entry Date
BKTXT CHAR 25 Document Header Text
WAERS CUKY 5 Currency Key
HWAER CUKY 5 Local Currency
KURS2 DEC 9 Exchange Rate for the Second Local
KURSF DEC 9 Exchange rate
USNAM CHAR 12 User name
BUZEI NUMC 3 Number of Line Item Within Accounting
HKONT CHAR 10 General Ledger Account
PRCTR CHAR 10 Profit Center
KOSTL CHAR 10 Cost Center
DMBE2 CURR 13 Amount in Second Local Currency
DMBTR CURR 13 Amount in local currency
SHKZG CHAR 1 Debit/Credit Indicator

note field SHKZG has either "S" or "H" as a value.

I need to find all entries for BELNR where the (SUM(DMBTR) for "SHKZG = S") NOT EQUAL (SUM(DMBTR) for "SHKZG = H")

I do not know how to structure the query, I can run the following and then try and find the values that do not match.

SELECT BELNR, SHKZG, SUM(DMBTR) as "TOTAL"
FROM Z_AUDIT_ALL_ENTRIES
GROUP BY BELNR, SHKZG
ORDER BY BELNR

Any help is appreciated.

Re: SQL Query Help [message #215306 is a reply to message #215305] Sat, 20 January 2007 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
use FROM Z_AUDIT_ALL_ENTRIES H, Z_AUDIT_ALL_ENTRIES S
Re: SQL Query Help [message #215307 is a reply to message #215306] Sat, 20 January 2007 19:03 Go to previous messageGo to next message
sbaracho
Messages: 3
Registered: January 2007
Junior Member
I appreciate your response but I still don't know how to construct the query.
Here is my updated query:

SELECT s.BELNR, s.SHKZG , SUM(s.DMBTR), h.BELNR, h.SHKZG , SUM(h.DMBTR)
FROM Z_AUDIT_ALL_ENTRIES s, Z_AUDIT_ALL_ENTRIES h
where
s.shkzg = 'S'
and
h.shkzg = 'H'
GROUP BY s.BELNR, h.belnr, s.SHKZG, h.SHKZG
ORDER BY s.BELNR, h.belnr





Thanks.

[Updated on: Sat, 20 January 2007 19:22]

Report message to a moderator

Re: SQL Query Help [message #215308 is a reply to message #215305] Sat, 20 January 2007 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Z_AUDIT_ALL_ENTRIES is the table name.
We agree on this fact.
>SHKZG is the field which can have either S or H.
We agree on this fact.
It appears you were not paying attention in class & have not read or understood the text book.
You can find the SQL Reference manual at http://tahiti.oracle.com
Study the section on the SELECT statement.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#SQLRF01702


t_alias

Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

[Updated on: Sat, 20 January 2007 19:24] by Moderator

Report message to a moderator

Re: SQL Query Help [message #215312 is a reply to message #215308] Sat, 20 January 2007 22:09 Go to previous message
sbaracho
Messages: 3
Registered: January 2007
Junior Member
I am sorry I don't know what u mean by class, I just posted on this forum for some help, I haven't taken any classes. Here is my updated query, it still doesn't work. I expect to get only 2 records back where the SUM for the SHKZG = S and SHKZG = H fields dont match but it gives me several results.


SELECT s.BELNR, s.SHKZG , SUM(s.DMBTR), h.BELNR, h.SHKZG , SUM(h.DMBTR)
FROM Z_AUDIT_ALL_ENTRIES s, Z_AUDIT_ALL_ENTRIES h
where
s.shkzg = 'S'
and
h.shkzg = 'H'
and
s.BUDAT = '20060101'
and
h.BUDAT = '20060101'
GROUP BY s.BELNR, h.belnr, s.SHKZG, h.SHKZG
having SUM(s.dmbtr) <> SUM(h.dmbtr)
order by s.BELNR asc, s.SHKZG, SUM(s.dmbtr) asc, h.belnr asc, h.SHKZG, SUM(h.dmbtr)
Previous Topic: [psp] -> trigger
Next Topic: Table creation help !!
Goto Forum:
  


Current Time: Tue Dec 06 02:42:17 CST 2016

Total time taken to generate the page: 0.09070 seconds