469,623 Members | 1,422 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

"HAVING" problems with query and using 'having' with group by

Hi,
I am trying to write a query in Oracle which I have not done before,
and are having some difficulty getting my result.

Please check my query and my results.

select max(note.datetime),
acgr.group_code,
bank.local_acc_no,
bank.short_name,
DECODE
(bank.account_type,
0,'Nostro',
1,'Internal',
2,'Safe Keeping',
3,'Draft',
4,'Vostro',
5,'Suspense',
6,'Exchange',
7,'Netting',
8,'Mutual Funds',
'UNKNOWN') AS "Acc Type",
item.gin,
item.value_date,
item.entry_date,
item.ls_type AS "LS Type",
item.currency AS "Currency",
item.amount AS "Amount",
item.sfield_7 AS "SField 7",
item.sfield_8 AS "SField 8",
item.sfield_9 AS "SField 9",
item.bran_code,
note.notetext
FROM
grup,
acgr,
bank,
item,
note
WHERE grup.group_code = acgr.group_code
AND acgr.corr_acc_no = bank.corr_acc_no
AND bank.corr_acc_no = item.corr_acc_no
AND item.corr_acc_no = note.corr_acc_no
AND item.gin = note.gin
AND item.flag_2 = 0
AND grup.group_code = 'GMSAZSGREC99'
and bank.account_type in (0)
group by acgr.group_code,
bank.local_acc_no,
bank.short_name,
bank.account_type,
item.gin,
item.value_date,
item.entry_date,
item.ls_type,
item.currency,
item.amount,
item.sfield_7,
item.sfield_8,
item.sfield_9,
item.bran_code,
note.notetext
a subset of the Results (I have taken some colums out to make it a
little
easier to read)

DATETIME Group Code Local A/c Num GIN Amount Bran Code NOTETEXT
16/10/2003 2:04:39 GMSAZSGREC99 26001777 2518 2140.4 AUSREG >> Source
Code was AUSRE
17/10/2003 6:06:34 GMSAZSGREC99 26001777 2518 2140.4 AUSREG Paul
chasing up with client
24/10/2003 0:28:33 GMSAZSGREC99 26001777 2554 3050000 AUSREG >> Source
Code was AUSRE
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2708 279.96 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:09 GMSAZSGREC99 25299777 2708 279.96 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2708 279.96 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2710 312.88 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:09 GMSAZSGREC99 25299777 2710 312.88 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2710 312.88 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2712 347.56 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:09 GMSAZSGREC99 25299777 2712 347.56 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2712 347.56 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2714 353.18 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2714 353.18 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2714 353.18 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2716 762.56 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2716 762.56 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2716 762.56 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:34 GMSAZSGREC99 25299777 2718 858.21 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2718 858.21 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2718 858.21 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:33 GMSAZSGREC99 25299777 2720 869.82 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2720 869.82 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2720 869.82 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:33 GMSAZSGREC99 25299777 2722 1998.92 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2722 1998.92 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2722 1998.92 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:33 GMSAZSGREC99 25299777 2724 2405.15 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:08 GMSAZSGREC99 25299777 2724 2405.15 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2724 2405.15 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 1:33:33 GMSAZSGREC99 25299777 2726 3151.99 AUSRE >> Source
Code was AUSRE
24/10/2003 0:29:07 GMSAZSGREC99 25299777 2726 3151.99 AUSRE >> Source
Code was AUSREG
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2726 3151.99 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 2:53:18 GMSAZSGREC99 25299777 2730 133.04 AUSTSG >> Source
Code was AUSRE
24/10/2003 3:02:37 GMSAZSGREC99 25299777 2750 261.29 AUSFU >> Source
Code was AUSRE
3/10/2003 7:04:21 GMSAZSGREC99 26001777 3920 512.86 AUSGV reg >>
Source Code was AUSRE
3/10/2003 7:04:35 GMSAZSGREC99 26001777 3920 512.86 AUSGV Wholeslae
registry to investigate and advise
8/10/2003 0:17:29 GMSAZSGREC99 26001777 3920 512.86 AUSGV W/reg to
transfer to Treasury Account
8/10/2003 6:35:48 GMSAZSGREC99 26001777 3920 512.86 AUSGV gv >> Source
Code was AUSREG
8/10/2003 6:36:26 GMSAZSGREC99 26001777 3920 512.86 AUSGV reg >>
Source Code was AUSGV
8/10/2003 6:36:49 GMSAZSGREC99 26001777 3920 512.86 AUSGV Wholesale
Registry investigating with GVC for reimbursement
14/10/2003 3:42:25 GMSAZSGREC99 26001777 3920 512.86 AUSGV Paul has
signed off to reimburse the portfolio >> Source Code was AUSREG
14/10/2003 3:42:28 GMSAZSGREC99 26001777 3920 512.86 AUSGV Paul has
signed off to reimburse the portfolio


What I want to achieve is all records with a maximum datetime for each
GIN showing the notetext details.

Example of what I want to achieve.

DATETIME Group Code Local A/c Num GIN Amount Bran Code NOTETEXT
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2708 279.96 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2710 312.88 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2712 347.56 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2714 353.18 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2716 762.56 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2718 858.21 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:32 GMSAZSGREC99 25299777 2720 869.82 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2722 1998.92 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2724 2405.15 AUSRE Email sent
to UTP to enter ledgers
24/10/2003 0:29:31 GMSAZSGREC99 25299777 2726 3151.99 AUSRE Email sent
to UTP to enter ledgers
22/10/2003 2:53:18 GMSAZSGREC99 25299777 2730 133.04 AUSTSG >> Source
Code was AUSRE
24/10/2003 3:02:37 GMSAZSGREC99 25299777 2750 261.29 AUSFU >> Source
Code was AUSRE
17/10/2003 6:06:34 GMSAZSGREC99 26001777 2518 2140.4 AUSREG Paul
chasing up with client
24/10/2003 0:28:33 GMSAZSGREC99 26001777 2554 3050000 AUSREG >> Source
Code was AUSRE
14/10/2003 3:42:28 GMSAZSGREC99 26001777 3920 512.86 AUSGV Paul has
signed off to reimburse the portfolio

I have got the above result by removing the notetext field from the
above query,
but I do require the field. I have also tried to use

having note.datetime = max(note.datetime)

In the above query but the version of Oracle (which i don't what is)
gives me
ORA-00979: not a GROUP BY expression. Which I don't get in Sybase...?

Any help would be much appreciated.
Thanks
GM
Jul 19 '05 #1
1 5145

Try this:

select max(note.datetime),

acgr.group_code,

bank.local_acc_no,

bank.short_name,

DECODE

(bank.account_type,

,'Nostro',

1,'Internal',

2,'Safe Keeping',

3,'Draft',

4,'Vostro',

5,'Suspense',

6,'Exchange',

7,'Netting',

8,'Mutual Funds',

'UNKNOWN') AS "Acc Type",

item.gin,

item.value_date,

item.entry_date,

item.ls_type AS "LS Type",

item.currency AS "Currency",

item.amount AS "Amount",

item.sfield_7 AS "SField 7",

item.sfield_8 AS "SField 8",

item.sfield_9 AS "SField 9",

item.bran_code,

note.notetext

FROM

grup,

acgr,

bank,

item,

note

WHERE grup.group_code = acgr.group_code

AND acgr.corr_acc_no = bank.corr_acc_no

AND bank.corr_acc_no = item.corr_acc_no

AND item.corr_acc_no = note.corr_acc_no

AND item.gin = note.gin

AND item.flag_2 = 0

AND grup.group_code = 'GMSAZSGREC99'

and bank.account_type in (0)

group by

acgr.group_code,

bank.local_acc_no,

bank.short_name,

DECODE

(bank.account_type,

,'Nostro',

1,'Internal',

2,'Safe Keeping',

3,'Draft',

4,'Vostro',

5,'Suspense',

6,'Exchange',

7,'Netting',

8,'Mutual Funds',

'UNKNOWN') AS "Acc Type",

item.gin,

item.value_date,

item.entry_date,

item.ls_type,

item.currency,

item.amount,

item.sfield_7,

item.sfield_8,

item.sfield_9,

item.bran_code,

note.notetext;
--
Posted via http://dbforums.com
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by malcolm | last post: by
13 posts views Thread by Jacek Dziedzic | last post: by
3 posts views Thread by =?Utf-8?B?QXhlbCBEYWhtZW4=?= | last post: by
5 posts views Thread by Cirene | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.