Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    23

    Unanswered: select query unique records

    Hi All

    I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. Can anyone help please? I have tried various ways using SQL developer but without success


    SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
    VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
    VE_ORDERLINE.DW_ORDER_NO,
    VE_ORDERLINE.PRD_SKN_NO,
    VE_ORDERLINE.ORDER_DATE
    FROM VE_ORDERLINE
    INNER JOIN VE_EZP_ORDER_TRANS
    ON VE_ORDERLINE.DW_ORDER_NO = VE_EZP_ORDER_TRANS.DW_ORDER_NO
    INNER JOIN VE_EZP_AGED_CUSTOMER_DEBT
    ON VE_EZP_ORDER_TRANS.DW_ORDER_NO = VE_EZP_AGED_CUSTOMER_DEBT.DW_ORDER_NO
    WHERE VE_ORDERLINE.PRD_SKN_NO = '400063'
    AND VE_ORDERLINE.ORDER_DATE >= TO_DATE('2013-03-01', 'YYYY-MM-DD')
    GROUP BY VE_ORDERLINE.DW_ORDER_NO
    HAVING (COUNT (VE_ORDERLINE.DW_ORDER_NO) > 1);

    Here is the error message I receive
    ORA-00979: not a GROUP BY expression
    00979. 00000 - "not a GROUP BY expression"
    *Cause:
    *Action:
    Error at Line: 1 Column: 8

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I want to only select one record where there may be several with the same dw_order_no
    so which DW_ORDER_NO should be the one in result set; random. highest, lowest?

    SELECT DISTINCT .......
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    23
    when i use the select statement below there are more than one record for each DW_ORDER_NO I want to only show one row for each DW_ORDER_NO, i'm not sure what you are asking? sorry! i have included an example of data below

    SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
    VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
    VE_ORDERLINE.DW_ORDER_NO,
    VE_ORDERLINE.ORDER_DATE,
    VE_ORDERLINE.PRD_SKN_NO,
    VE_EZP_AGED_CUSTOMER_DEBT.MEMBER_NO_WCD,
    VE_ORDERLINE.ORDER_NO
    FROM VE_ORDERLINE
    INNER JOIN VE_EZP_ORDER_TRANS
    ON VE_ORDERLINE.DW_ORDER_NO = VE_EZP_ORDER_TRANS.DW_ORDER_NO
    INNER JOIN VE_EZP_AGED_CUSTOMER_DEBT
    ON VE_EZP_ORDER_TRANS.DW_ORDER_NO = VE_EZP_AGED_CUSTOMER_DEBT.DW_ORDER_NO
    WHERE VE_EZP_ORDER_TRANS.EZP_BILL_STATUS = 'AT'
    AND VE_ORDERLINE.ORDER_DATE >= TO_DATE('2013-03-01', 'YYYY-MM-DD')
    AND VE_ORDERLINE.PRD_SKN_NO = '400063'


    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017
    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017
    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017
    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017
    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017
    AT SOMERS Current Debt 201304730 28-APR-13 400063 18936 73017

  4. #4
    Join Date
    Sep 2013
    Posts
    23
    Great this worked better! thank you, but still getting some multiples do i need some brackets so i am only selecting distinct DW_ORDER_NO though as I now seem to be selecting where all columns are distinct
    SELECT DISTINCT VE_EZP_ORDER_TRANS.DW_ORDER_NO, VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
    VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
    VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
    VE_ORDERLINE.DW_ORDER_NO,
    VE_ORDERLINE.ORDER_DATE,
    VE_ORDERLINE.PRD_SKN_NO,
    VE_EZP_AGED_CUSTOMER_DEBT.MEMBER_NO_WCD,
    VE_ORDERLINE.ORDER_NO
    FROM VE_ORDERLINE
    INNER JOIN VE_EZP_ORDER_TRANS
    ON VE_ORDERLINE.DW_ORDER_NO = VE_EZP_ORDER_TRANS.DW_ORDER_NO
    INNER JOIN VE_EZP_AGED_CUSTOMER_DEBT
    ON VE_EZP_ORDER_TRANS.DW_ORDER_NO = VE_EZP_AGED_CUSTOMER_DEBT.DW_ORDER_NO
    WHERE VE_EZP_ORDER_TRANS.EZP_BILL_STATUS = 'AT'
    AND VE_ORDERLINE.ORDER_DATE >= TO_DATE('2013-03-01', 'YYYY-MM-DD')
    AND VE_ORDERLINE.PRD_SKN_NO = '400063';

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you could use ROWID to restrict which rows are returned
    Data Types
    ROWID Pseudocolumn
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2013
    Posts
    23
    Thanks for your help! i managed to settle for an alternative solution by deleting some of the columns it resolved the issue

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •