Re: Scalar Functions in Group By Clause returning invalid values

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/04/05 

Date: Tue, 04 Jan 2005 22:14:45 +0100

On Tue, 4 Jan 2005 03:29:01 -0800, mikekimber1 wrote:

> *** SORRY CORRECT SQL STATEMENT!! ***
>
>SQL Statement :
>
>SELECT
>test.FLODS_UTL_LKUP_VALUE_FUNC('LGNCC_INTLOGHDR','CLIENTTYPE',t1.CLIENTTYPE),
>
>test.FLODS_UTL_LKUP_VALUE_FUNC('LGNCC_INTLOGHDR','INITCHANNEL',t1.INITCHANNEL),
> count(1) As Total
>FROM LGNCC_INTLOGHDR t1
>GROUP BY
>test.FLODS_UTL_LKUP_VALUE_FUNC('LGNCC_INTLOGHDR','CLIENTTYPE',t1.CLIENTTYPE),
>
>test.FLODS_UTL_LKUP_VALUE_FUNC('LGNCC_INTLOGHDR','INITCHANNEL',t1.INITCHANNEL)
>ORDER BY 1,2

Hi Mike,

Are you aware that the function will be executed twice for each row in
LGNCC_INTLOGHDR? I'm afraid that your query won't scale well...

If I read the code of your function correctly, I think you could change
the above query to the following, using joins instead of your user-defined
function:

SELECT COALESCE (ct.DISPLAY_NAME, 'Unspecified') AS ClientType,
          COALESCE (ic.DISPLAY_NAME, 'Unspecified') AS InitChannel,
          COUNT(*) AS Total
FROM LGNCC_INTLOGHDR AS t1
LEFT JOIN FLODS_UTL_LOOKUP_CODE AS ct
     ON ct.LOOKUP_GROUP = 'LGNCC_INTLOGHDR'
     AND ct.SUB_GROUP = 'CLIENTTYPE'
     AND ct.LOOKUP_VALUE = t1.CLIENTTYPE
LEFT JOIN FLODS_UTL_LOOKUP_CODE AS ic
     ON ic.LOOKUP_GROUP = 'LGNCC_INTLOGHDR'
     AND ic.SUB_GROUP = 'INITCHANNEL'
     AND ic.LOOKUP_VALUE = t1.INITCHANNEL
GROUP BY COALESCE (ct.DISPLAY_NAME, 'Unspecified'),
          COALESCE (ic.DISPLAY_NAME, 'Unspecified')
ORDER BY 1, 2
(untested)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Automatic File Relocation Governing Rules
    ... I genuinely couldn't find anything directly related to this query. ... I'm inexprienced in the correct searching techniques. ... "Mike Williams " wrote: ... This is frustrating! ...
    (microsoft.public.windowsmedia.player)
  • RE: Sort unbonded combo box data
    ... Mike I need to do this programmatically. ... A Query ... Dim strColumnsNames As String ... ' Set the strColumnsNames string as the data source of the combo box ...
    (microsoft.public.access.forms)
  • Re: SQL null behavior with DATE columns
    ... The IS NOT NULL is testing the date_expression which is assured by the COALESCE() in the NTE, to never be the NULL value. ... Rows where the termination date is GREATER than or EQUAL to ... Selection on an invalid date value does not allow the query engine to determine if the row should be included or omitted, since its value can not be determined. ...
    (comp.sys.ibm.as400.misc)
  • Re: This one is a tough one...
    ... Thanks Mike for what seems to be a brilliant solution! ... > Saved under the name XTerrorCode, the query we need is then: ... > each of these error code should not appear more than once. ...
    (microsoft.public.access.queries)
  • Re: Further Help Please : SQL
    ... Generally I don't like the non SQL standard behaviour of MS access ... "Mike Wilson" wrote in message ... > details to a new order but if I clone the order I also ... >> query icon on the toolbar at the top of the screen and change the query ...
    (microsoft.public.access.gettingstarted)