List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 9 2002 3:25pm
Subject:Re: User Variables
View as plain text  
At 9:50 -0400 5/9/02, Luc Foisy wrote:
>I am trying to use user variables in the same select statement I "create"
>the variable in.
>see select statement below
>
>what seems to be happening is the variable is not available until the next
>time I run the select statement. (it is using old data)
>is this a bug or is it intentional? The documentation reads:
>Note: in a SELECT statement, each expression is evaluated only when it's
>sent to the client. This means that in the HAVING, GROUP BY, or ORDER BY
>clause, you can't refer to an expression that involves variables that are
>set in the SELECT part. For example, the following statement will NOT work
>as expected:
>mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
>The reason is that @aa will not contain the value of the current row, but
>the value of id for the previous accepted row.
>
>I am not using HAVING, GROUP BY, or ORDER BY

No, but it appears you are trying to use a value that you are selecting
as a value that is used to determine which values are selected, which
doesn't make any sense.

>
>mysql  Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
>
>SELECT @AddressJoinID := IF (CUSTOMER.ID_ADDRESS_BILLTO > 0,
>CUSTOMER.ID_ADDRESS_BILLTO, CUSTOMER.ID_ADDRESS) AS 'Junk', INVOICEHEADER.ID
>AS 'ID_INVOICEHEADER', DATE_FORMAT(INVOICEHEADER.Date,'%Y/%m/%d') AS
>'Invoice_Date', INVOICEHEADER.ID_CUSTOMER AS 'ID_CUSTOMER',
>INVOICEHEADER.SubTotal, INVOICEHEADER.Tax1Total AS 'Tax1Total',
>INVOICEHEADER.GrandTotal AS 'GrandTotal', CUSTOMER.Company AS
>'Cust_Company', CUSTOMER.*, COMPANY.*, TRIM(CONCAT(IF(ADDRESS.UnitNumber IS
>NULL,'',ADDRESS.UnitNumber),' ',ADDRESS.CivicNumber,' ',STREET.Name,'
>',IF(STREETTYPE.Abv IS NULL,'',STREETTYPE.Abv))) AS 'Address_1',
>ADDRESS.Line2 AS 'Address_2', CONCAT(CITY.Name,', ',PROVINCE.Name) AS
>'Address_3', ADDRESS.PostalCode AS 'Cust_PostalCode' FROM INVOICEHEADER LEFT
>JOIN CUSTOMER ON INVOICEHEADER.ID_CUSTOMER = CUSTOMER.ID LEFT JOIN ADDRESS
>ON ADDRESS.ID = @AddressJoinID LEFT JOIN STREET ON ADDRESS.ID_STREET =
>STREET.ID LEFT JOIN STREETTYPE ON ADDRESS.ID_STREETTYPE = STREETTYPE.ID LEFT
>JOIN CITY ON ADDRESS.ID_CITY = CITY.ID LEFT JOIN PROVINCE ON
>ADDRESS.ID_PROVINCE = PROVINCE.ID LEFT JOIN COUNTRY ON ADDRESS.ID_COUNTRY =
>COUNTRY.ID LEFT JOIN COMPANY ON CUSTOMER.ID_COMPANY = COMPANY.ID WHERE
>INVOICEHEADER.ID =
>
>Luc Foisy
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread108486@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
User VariablesLuc Foisy9 May
  • Re: User VariablesPaul DuBois9 May
RE: User VariablesLuc Foisy9 May
  • RE: User VariablesPaul DuBois9 May
RE: User VariablesLuc Foisy9 May
  • RE: User VariablesPaul DuBois9 May