Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

if ... field is null then field = 'Some Text'

Posted on 2000-05-09
10
Medium Priority
?
364 Views
Last Modified: 2012-05-04
part of my sql statement is

select jobs.sqlkey, jobs.Job__, jobs.Name, jobfilelocations.person,  
jobfilelocations.datetaken, FileBIN = JobFileLocations.sqlKey from jobs
left outer join Jobfilelocations on jobs.sqlkey = jobfilelocations.jobIDx and
(returned = 0 or returned is null)

I want to add something that would make the jobfilelocations.person = 'Filed'  if the (retruned is null)

the rest of the sql statement works perfectly, I just need to add something that I am not sure how to do
0
Comment
Question by:RoLaAus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 1

Expert Comment

by:bbychkov
ID: 2793571
You can use IsNull in the select list:
select IsNull(jobfilelocations.person, 'Filed') As Person
or you can use CASE:
select CASE WHEN returned IS NULL THEN 'Filed' ELSE jobfilelocations.person END from ...


   
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 200 total points
ID: 2793615
Your syntax would be like this:

select jobs.sqlkey, jobs.Job__, jobs.Name,
PersonValue = CASE
  WHEN returned Is Null Then 'Filed'
  ELSE jobfilelocations.person
END,  
jobfilelocations.datetaken, FileBIN = JobFileLocations.sqlKey from jobs
left outer join Jobfilelocations on jobs.sqlkey = jobfilelocations.jobIDx and
(returned = 0 or returned is null)


The IsNull would not work for this, since it would return the Returned value if returned was not null, and you need the value in the field person.
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2793640
RoLaAus,

If I understood your request you should do something like this:

select jobs.sqlkey
      ,jobs.Job
      ,jobs.Name
      ,jobfilelocations.person
      ,jobfilelocations.datetaken
      ,FileBIN=JobFileLocations.sqlKey
  from jobs
       join Jobfilelocations
         on jobs.sqlkey=jobfilelocations.jobIDx
 and (returned=0 or returned is null)
 and (jobfilelocations.person
     =case when returned is null
        then 'Filed'
        else jobfilelocations.person
      end)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2793667
bhess1,
I didn’t reload the question before my comment was posted.
0
 
LVL 1

Expert Comment

by:bbychkov
ID: 2793764
bhess1,
If "returned is null" states for records from jobs that don't have matching records in jobfilelocations (remember, it's left outer join), then jobfilelocations.person is null for these records as well. Then "IsNull(jobfilelocations.person, 'Filed') As Person" will work.
0
 

Author Comment

by:RoLaAus
ID: 2793834
bbychkov,

is the 'left outer join' appropriate for this?

I am only vaguely familiar with join statements, and usually build the sql using the query building in Access then convert any syntax over to be compatible in SQL

so I am not sure which JOIN type is appropriate, all I know right now is that it work <G>  but it might work better, and I am not aware of it.

I will grade your comment as soon as I verify that it works (I switched projects temporarily), but it looks fine - logic wise
0
 

Author Comment

by:RoLaAus
ID: 2793864
thanks, it worked like a charm

I looked for the IF statement in the SQL help in Enterprise Manager, but it didn't give a sample statement

Of course, I should have looked for CASE (had I known about it) but figured i would find the answer quicker here
0
 
LVL 1

Expert Comment

by:bbychkov
ID: 2793869
RoLaAus,
left outer join will give you all records from jobs table and only those records from Jobfilelocations table that have jobfilelocations.jobIDx = jobs.sqlkey. If record from jobs doesn't have matching record in Jobfilelocations then all fileds from Jobfilelocations will be returned as NULL (and IsNull function will replace them with value that you specify). If you need only records from jobs table that have matches in Jobfilelocations then you have to use inner join.
0
 

Author Comment

by:RoLaAus
ID: 2794076
I guess I should have clearified, I do need all job records

we need to know if certain files are on peoples desk (returned would be 0 and their name in the Person field)

or if no one has the job, it would be in the Files - that is why I wanted to add the 'Filed'  into the person field, ppl were getting confused by a blank field wondering where the file is located.

thanks again
0
 
LVL 1

Expert Comment

by:bbychkov
ID: 2794107
Than left outer join is fine. And both IsNull and CASE will do it for you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question