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