This topic is locked
[SOLVED]

 using CONCAT on 'as' fields

2/11/2012 4:46:06 AM
PHPRunner General questions
M
miles authorDevClub member

Hi i am trying to get a field in my SQL which incorporates some 'calculated' fields. is this possible?
i have a drop down list 'Nursing' and some tickboxes 'AD' 'D' 'DD' etc, i want to generate a field which includes certain key words if the boxes are ticked...
eg

if(Nursing="YES", 'Nursing ', '') AS spec1, (if the nursing ddl is yes then Nursing otherwise empty field called spec1)

if(AD=1, 'Alcohol Dependancy ', '') AS spec2, (if the AD box is ticked then Alcohol Dependency otherwise empty field called spec2)
i then want to add them all together to get a full spec for the care home and show as a field called CHspec....
so i am looking for a field that says perhaps 'Nursing, Alcohol Dependancy, another field, another field'
however when i try to CONCAT as follows CONCAT(spec1, ', ', spec2, ', ', spec3) AS CHspec, i get an error 'unknown column 'spec1' in field list'
Any help appreciated full SQL follows:
SELECT

ID,

Title,

Address1,

Address2,

Location,

Postcode,

Telephone,

Email,

Manager,

Description,

Nursing,

Status,

AD,

D,

DD,

LD,

MH,

NMI,

OAO,

PD,

SI,

image,

if(Nursing="YES", 'Nursing', '') AS spec1,

if(AD=1, 'Alcohol Dependancy ', '') AS spec2,

if(D=1, 'Dementia', '') AS spec3,

CONCAT(spec1, ', ', spec2, ', ', spec3) AS CHspec,

CONCAT(address1, ', ', location, ', ', postcode) AS FullAddress,

map

FROM care_homes

Sergey Kornilov admin 2/11/2012

Some databases won't allow you to use aliases when you define a new calculated field. Use a full calculated field specification instead i.e.

CONCAT(if(Nursing="YES", 'Nursing', ''), ', ', if(AD=1, 'Alcohol Dependancy ', ''), ', ', if(D=1, 'Dementia', '') ) AS CHspec,
M
miles authorDevClub member 2/12/2012

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=64285&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' /> Thanks a bunch Sergey, you are a genius! this one is solved!