2. PHPRunner General questions
  3. [SOLVED]  using SQL variable and CASE statement in query
This topic is locked
[SOLVED]

 using SQL variable and CASE statement in query

 		1/31/2021 6:54:53 PM
PHPRunner General questions
R
 rmac author

I'm trying to update an old version of eGroupWare we've been using for years by building a PHPR project around the existing tables. One of the features they had in their Addressbook was a field identifying a preferred telephone number for the contact. What I'm trying to do is select the preferred number from the Addressbook table when using it as a lookup for a Tracker entry as defined by the "tel_prefer" field (which only lists the key name of the preferred number). I managed to write a query in the query builder that outputs what I need in the variable field, but the application throws a syntax error; likely because it passes the whole CASE ststement as the variable. This is my first attempt at using either SQL variables OR a SQL CASE statement so it may not be possible or incorrect format, but I'm guessing there are several (some probably much smarter) ways to do this so I'm looking for input. Any suggestions will be appreciated. Thanks.



SELECT

contact_id,

tel_work,

tel_cell,

tel_home,

tel_prefer,

@number := (CASE

WHEN tel_prefer = "tel_cell" THEN @contact_num := tel_cell

WHEN tel_prefer = "tel_work" THEN @contact_num := tel_work

WHEN tel_prefer = "tel_home" THEN @contact_num := tel_home

WHEN tel_prefer = "" THEN @contact_num := ""

END)

FROM addressbook
A
 		acpan 1/31/2021

You may not need to use SQL "User-Defined " variables (@number, @contact_num .. etc.) at all, although it went without error on my PHPRunner 10.05 when i replicate your statement.
SQL's user-defined variable store a value in one statement and refer to it later in another statement. This enables you to pass values from one statement to another within eg. store procedures, or used to evaluate a result in a subquery that passes to the main query.
But it seems it is not necessary for your case. You can simply extract the contact_num, based on values of another field without SQL's user-defined variables:



SELECT

contact_id,

tel_work,

tel_cell,

tel_home,

tel_prefer,

(CASE

WHEN tel_prefer = "tel_cell" THEN tel_cell

WHEN tel_prefer = "tel_work" THEN tel_work

WHEN tel_prefer = "tel_home" THEN tel_home

WHEN tel_prefer = "" THEN ""

END)as contact_num

FROM addressbook


Check this tutortial how to use MySQL Case When statement.
R
 		rmac author 2/1/2021



You may not need to use SQL "User-Defined " variables (@number, @contact_num .. etc.) at all, although it went without error on my PHPRunner 10.05 when i replicate your statement.
SQL's user-defined variable store a value in one statement and refer to it later in another statement. This enables you to pass values from one statement to another within eg. store procedures, or used to evaluate a result in a subquery that passes to the main query.
But it seems it is not necessary for your case. You can simply extract the contact_num, based on values of another field without SQL's user-defined variables:



SELECT

contact_id,

tel_work,

tel_cell,

tel_home,

tel_prefer,

(CASE

WHEN tel_prefer = "tel_cell" THEN tel_cell

WHEN tel_prefer = "tel_work" THEN tel_work

WHEN tel_prefer = "tel_home" THEN tel_home

WHEN tel_prefer = "" THEN ""

END)as contact_num

FROM addressbook


Check this tutortial how to use MySQL Case When statement.


acpan;
You are indeed correct! That works exactly as I had hoped. I thought I'd need the variable as a field reference; didn't realize that you could just assign an alias.

I think I'll be using this quite a bit in the future. Thanks so much for the quick response and the help.