SQL - Return a field value if it exists, otherwise, return a specific text.

background info, i am trying to create a list view that shows thumbail images, if they have been created (and image name saved in my table).  

I'm wonderinig if there is an easy way I can do this, so it works with both access and sql server, so that I can achieve this logic:

Select all my courses from the course list, left joining with my section table, which has the thumbnail image name stored in one column (but may be blank).  There is a chance no record is returned from this 2nd table at all.

In the query below, i want Thumbnail's value to be the image name returned, OR "NoThumbnailYet.jpg", if it returns no value.

Select C.Course_Number, S.Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Is there a basic syntax I should use, or is this something I am better off doing after returning the full query, during building  my data table to bind to the list view.  I know I can do the manipulating after the fact, just was hoping for a more simple solution.

Robert HamelSenior .NET developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
In Access, the function is NZ().  In SQL Server, it is COALESCE()

Select C.Course_Number, NZ(S.Thumbnail, "NoThumbnailYet.jpg"
From Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no issue:
Select C.Course_Number, COALESCE(S.Thumbnail, 'NoThumbnailYet.jpg') Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Open in new window

0
Dale FyeCommented:
oops, missing closing parenthesis

Select C.Course_Number, NZ(S.Thumbnail, "NoThumbnailYet.jpg")
From Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Another method for handling this, if you want to use the same syntax for Access and SQL Server backends is to use your original query, and then use the Current event of your form to test for the value of the field [Thumbnail] before setting the Picture property of an image control.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Robert HamelSenior .NET developerAuthor Commented:
apparently the NZ function doesn't work outside of access itself.  Using it in ASP application is not supported... getting:  Undefined function 'NZ' in expression

Microsoft says it isn't supported:
http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc
0
RyanProject Engineer, ElectricalCommented:
If speed isn't too big of a deal, you can also use
SELECT...IIF(S.Thumbnail<>Null,S.Thumbnail,"NoThumbnailYet.jpg")...

This code would be run client side, so its a bit slower.
0
worthyking1Commented:
You can also simply do it while you're looping through the recordset to create your list view, for example:

set rs = con.execute("Select C.Course_Number, S.Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number")

while not rs.eof

  ' populate your vars
  Course_Number = CInt(rs("Course_Number"))
  If Trim(rs("Thumbnail"))<>"" then Thumbnail = Trim(rs("Thumbnail")) else Thumbnail = "NoThumbnailYet.jpg"
  Sections = rs("Sections")  

  ' now do your list output however you want using the vars, for example
  response.write "Course number: " & Course_Number & "<br>"
  response.write "<img src=""" & Thumbnail  & """><br>"

rs.movenext()
wend
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert HamelSenior .NET developerAuthor Commented:
NZ didn't work for a web app, but was still good to know.  Coalesce works great.  In the end, being able to have the same logic for both access and SQL server was ideal for simplicity, so I went with something similar to the loop.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.