Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Unanswered: Upper case in GROUP BY statement

    Hi Guys,

    Because some n00b coder didn't force user input to be upper case before inserting into the database, I'm forced to deal with an issue. I'm forcing to columns to upper case in my SELECT section but I need to be able to force the upper case in my GROUP BY as well, so that it will combine like records correctly. Tried using UPPER(column_name) in the GROUP BY but quickly found that Informix does not like it.

    QUERY:
    SELECT ddh_invc, ddh_store as storeNum, ddh_ticket as ticketNum, ddh_ticket_sub as ticketSub,
    dmh_invc_date as invcDate, dmh_ship_date as shipDate, ddh_need_dt as needDate,
    ddh_ord_dt as ordrDate, dmh_cust_no as custNum, UPPER(dmh_user) as initBy, UPPER(ddh_add_user) as crtdBy,
    dmh_add_time as addTime, SUM(ddh_net_total) as netTotal, COUNT(ddh_line) as lineItems
    FROM dmh o, ddh c
    WHERE ddh_ticket = dmh_ticket
    AND ddh_ticket_sub = dmh_ticket_sub
    AND ddh_store = dmh_store
    AND dmh_invc_date >= '05-01-2007'
    AND dmh_invc_date <= '05-31-2007'
    AND dmh_status = '50'
    AND dmh_store = '34'
    GROUP BY ddh_invc, ddh_store, ddh_ticket, ddh_ticket_sub, dmh_invc_date, dmh_ship_date,
    ddh_need_dt, ddh_ord_dt, dmh_cust_no, dmh_add_time, dmh_user, ddh_add_user
    ORDER BY storeNum, ticketNum, ticketSub ASC

    Thanks

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    And what happens if you replace the columnnames in the GROUP BY clause with their positional numbers in the SELECT clause?
    Like:
    Code:
    SELECT ddh_invc, ddh_store as storeNum, ddh_ticket as ticketNum, ddh_ticket_sub as ticketSub,
    dmh_invc_date as invcDate, dmh_ship_date as shipDate, ddh_need_dt as needDate,
    ddh_ord_dt as ordrDate, dmh_cust_no as custNum, UPPER(dmh_user) as initBy, UPPER(ddh_add_user) as crtdBy,
    dmh_add_time as addTime, SUM(ddh_net_total) as netTotal, COUNT(ddh_line) as lineItems
    FROM dmh o, ddh c
    WHERE ddh_ticket = dmh_ticket
    AND ddh_ticket_sub = dmh_ticket_sub
    AND ddh_store = dmh_store
    AND dmh_invc_date >= '05-01-2007'
    AND dmh_invc_date <= '05-31-2007'
    AND dmh_status = '50'
    AND dmh_store = '34'
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
    ORDER BY storeNum, ticketNum, ticketSub ASC
    This way the results are not grouped by the raw columnvalues but by the converted results of the SELECT list. You could also group by the columnlabels in your SELECT.

    Regards

  3. #3
    Join Date
    Jun 2007
    Posts
    3
    OMG that worked! Tyveleyn thanks a lot, it has been bugging me for the last two days. If you don't mind, explain how using the ordered numbers instead, works again?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by spencet
    Because some n00b coder didn't force user input to be upper case before inserting into the database...
    HOLD IT RIGHT THERE MATEY!
    All formatting to text is to be done in the front-end application (presentation-layer). SQL should not be used to format text in any way, shape or form.
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    Quote Originally Posted by georgev
    HOLD IT RIGHT THERE MATEY!
    All formatting to text is to be done in the front-end application (presentation-layer). SQL should not be used to format text in any way, shape or form.
    Oh, believe me, I fully agree with you. That's what I meant.

Posting Permissions

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