Substitute one columns results for another

I asked this already in the database forum, so I'm sorry for "double posting"

But, anyway...

I'm having a trouble with this SQL statment:

SELECT USERID, USER_LOGINID, LASTNAME, CITY, STATE, FIRSTNAME, IF (DISPLAYNAME IS NULL, FIRSTNAME, DISPLAYNAME)
FROM USER_LOGIN

WHERE Left(FIRSTNAME, 1) LIKE '%#firstletter#%' 

AND STATE LIKE '%#STATE#%' 
AND CITY LIKE '%#CITIES#%' 

ORDER BY <cfoutput>#ORDERBY#</cfoutput>

Code (markup):

I just want to return data from the two columns "firstname & displayname"
but in the case where the displayname column is null I want to substitute it for the data from the firstname column.

Very simalar to COALESCE but with two columns instead.

 

change your

(if displayname is null, firstname, displayname)

to

CASE WHEN displayname IS null THEN firstname ELSE displayname END AS displayname

Code (markup):

this works in mssql at least

 

The CASE statement should do exactly what you need, but in case you're curious you can use 2 column names in the COALESCE function. Its basically equivalent to the CASE statement Jamie18 posted

SELECT   COALESCE(DISPLAYNAME, FIRSTNAME) AS DisplayName
.....

Code (markup):

Also, something seems a little off with this statement.

WHERE Left(FIRSTNAME, 1) LIKE '%#firstletter#%'

Code (markup):

If you're trying to find firstName's where the 1st character = #firstLetter# do either

WHERE Left(FIRSTNAME, 1) = '#firstletter#'    

Code (markup):

OR

WHERE FIRSTNAME LIKE '#firstletter#%'    

Code (markup):

 

Both statments are working but not completly. They do the exact same thing just like you said they would.

If DISPLAYNAME is null then no result is returned.

If there is data there, then it does return the displayname column info.

But neither will substitute firstname for displayname when displayname in null.

Heres what the sql looks like with both of your suggestions:


Jamie18:

SELECT CASE WHEN displayname IS null THEN firstname ELSE displayname END AS displayname, USERID, LASTNAME, CITY, STATE
FROM USER_LOGIN
WHERE USERID LIKE '#USERID#'

Code (markup):

CfStarlight:

SELECT COALESCE(DISPLAYNAME, FIRSTNAME) AS DisplayName, USERID, LASTNAME, CITY, STATE
FROM USER_LOGIN
WHERE USERID LIKE '#USERID#'

Code (markup):

Thanks again to both of you.

ULB wouldnt be where we're at if it wasnt for kind folk such as yourself.

 

On a second thought, I bet a simple <cfif> tag would work...

Like

<cfif displayname eq "">
#FIRSTNAME# #LASTNAME#

<cfelse>

#DISPLAYNAME#

</cfif>

Code (markup):

I'll try this out, although I do enjoy learning better techniques for writing functional SQL statments.

 

Yeah this does work to accomplish just substituting the names...

but it doesnt fix another problem I was trying to get around by having a well writen sql....

 

Yeah... feeling dumb is always apart of learning.

Both of your Sql statment are working fine.

CfStarlight your supision about the WHERE clause was acurate.

It was actually the reason I said earlier that neither sql was working.

So I deleted it completely and now everthing if working great.

Here are the two statments that are working:

CfStarlight's:

SELECT COALESCE(DISPLAYNAME, FIRSTNAME) AS DisplayName, USERID, USER_LOGINID, LASTNAME, CITY, STATE, ZONE_OFFSETID, LAST_LOGIN_DATE
FROM USER_LOGIN

WHERE STATE LIKE '%#STATE#%' 
AND CITY LIKE '%#CITIES#%' 

ORDER BY <cfoutput>#ORDERBY#</cfoutput>

Code (markup):

Jaime18:

SELECT CASE WHEN displayname IS null THEN firstname ELSE displayname END AS displayname, USERID, LASTNAME, CITY, STATE
FROM USER_LOGIN
WHERE USERID LIKE '#USERID#'

Code (markup):

Good work guys...

You can see both these satements in action @

http://www.unitedlocalbands.com/user_personal_logo.cfm

&

http://www.unitedlocalbands.com/use...fm?userid=8A7AAED8-C988-CA4E-FBE89B8087436B79

 

Not to nitpick but for performance reasons its best not to use LIKE unless its needed. LIKE doesn't serve a purpose here because there are no wildcards. So its the same as saying

WHERE USERID = '#USERID#'

Code (markup):

 

Thanks for the tip, it make a difference.

I have been going through every sql statment on the site, and seeing that i used LIKE in almost every single one.

So I been changing all the LIKE operators to = .

I also started to chang the layout of the whole site so it will be more cross browser friendly.

If you ever feel like signing up and checking things out, please feel free to do so.

You can also send messages to me directly on unitedlocalbands.com

 

Cool. Thanks for the invite