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

Hi,

And what happens if you replace the columnnames in the GROUP BY clause with their positional numbers in the SELECT clause?
Like: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

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?

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.