SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    naperville
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Alias + functions in select?

    Hi, I have a query that goes something like this:

    Code:
    SELECT
    	  IF(...) as user
    	, IF(...) as G1
    	, IF(...) as G2
    FROM 
    	TABLES
    WHERE
    	....
    The if statements contain a nested if statements as well as nested subqueries, and this has all been edited out in favor of breviety.

    What I need to do is this

    Code:
    SELECT
    	  IF(...) as user
    	, IF(...) as G1
    	, IF(...) as G2
    	, (G1 + G2 .... + GN) as group_aggregate
    	, IF(statement involving group_aggregate and user)
    FROM 
    	TABLES
    WHERE
    	....
    The issue appears to be that you cannot aliased function results in other functions in the same select statement. I could drop aliases, however, this would lead to some repeated code (several more subqueries per query).

    Is there another way to reference function results as to use them in other functions in the same query? Perhaps a numerical index? Any help would be appreciated.

    MySQL gives this issue btw:
    #1054 - Unknown column 'user' in 'field list'

    Thanks!

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT user,g1,g2, g1+g2 as correlationName
      from (select 
    	  case ... end as user
    	, case ... end as G1
    	, case ... end as G2
    FROM  	TABLES
    WHERE
    	....) dt
    do yourself a favour and stop using if. Case expressions can be used with the same functionality and do function in other DBMSs.


Bookmarks

Posting Permissions

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