RoLaAus
asked on
if ... field is null then field = 'Some Text'
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
select jobs.sqlkey, jobs.Job__, jobs.Name, jobfilelocations.person,
jobfilelocations.datetaken
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RoLaAus,
If I understood your request you should do something like this:
select jobs.sqlkey
,jobs.Job
,jobs.Name
,jobfilelocations.person
,jobfilelocations.datetake n
,FileBIN=JobFileLocations. sqlKey
from jobs
join Jobfilelocations
on jobs.sqlkey=jobfilelocatio ns.jobIDx
and (returned=0 or returned is null)
and (jobfilelocations.person
=case when returned is null
then 'Filed'
else jobfilelocations.person
end)
If I understood your request you should do something like this:
select jobs.sqlkey
,jobs.Job
,jobs.Name
,jobfilelocations.person
,jobfilelocations.datetake
,FileBIN=JobFileLocations.
from jobs
join Jobfilelocations
on jobs.sqlkey=jobfilelocatio
and (returned=0 or returned is null)
and (jobfilelocations.person
=case when returned is null
then 'Filed'
else jobfilelocations.person
end)
bhess1,
I didn’t reload the question before my comment was posted.
I didn’t reload the question before my comment was posted.
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.p erson, 'Filed') As Person" will work.
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.p
ASKER
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
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
ASKER
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
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
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.
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.
ASKER
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
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
Than left outer join is fine. And both IsNull and CASE will do it for you.
select IsNull(jobfilelocations.pe
or you can use CASE:
select CASE WHEN returned IS NULL THEN 'Filed' ELSE jobfilelocations.person END from ...