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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • George
    #1

    "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.dateti me),
    acgr.group_code ,
    bank.local_acc_ no,
    bank.short_name ,
    DECODE
    (bank.account_t ype,
    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_n o = bank.corr_acc_n o
    AND bank.corr_acc_n o = item.corr_acc_n o
    AND item.corr_acc_n o = note.corr_acc_n o
    AND item.gin = note.gin
    AND item.flag_2 = 0
    AND grup.group_code = 'GMSAZSGREC99'
    and bank.account_ty pe in (0)
    group by acgr.group_code ,
    bank.local_acc_ no,
    bank.short_name ,
    bank.account_ty pe,
    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.dateti me)

    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
    Tags: None
    • LKBrwn_DBA
      #2
      Re: "HAVING&qu ot; problems with query and using 'having' with group by


      Try this:

      select max(note.dateti me),

      acgr.group_code ,

      bank.local_acc_ no,

      bank.short_name ,

      DECODE

      (bank.account_t ype,

      ,'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_n o = bank.corr_acc_n o

      AND bank.corr_acc_n o = item.corr_acc_n o

      AND item.corr_acc_n o = note.corr_acc_n o

      AND item.gin = note.gin

      AND item.flag_2 = 0

      AND grup.group_code = 'GMSAZSGREC99'

      and bank.account_ty pe in (0)

      group by

      acgr.group_code ,

      bank.local_acc_ no,

      bank.short_name ,

      DECODE

      (bank.account_t ype,

      ,'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

        Comment

        Previous template Next
        Working...