1

I have table with field update_ version which can have null and some value.. 

Select * from Employee where employee_id =  '1234' and updated_version is null

Select * from Employee where employee_id =  '1234' and updated_version ='1'

I have to combine both the queries

I have tried like this but didn't work.

Select * from Employee 
where employee_id =  '1234' 
and updated_version = (select case when updated_version is null 
                              then NULL 
                              else updated_version 
                              end)

Note: I wil get inputs for employee id and updated_version from other table

Sorry for the Confusion. The updated Version value is not only 1 and null. It can be any value which i'll get from another table. So what ever value i'm getting from other table, I have to query with that condition in Employee table, to get the appropriate result. Not all the result.

Improve this question
2
  • 1
    Did you try what Parado said ? Commented Jul 29, 2013 at 19:40
  • 1
    Show your output table structure. Commented Jul 29, 2013 at 19:41

3 Answers 3

Reset to default
2

As others have mentioned, a bit unclear as to what you want in the output, but if I get you correctly, you want the rows that have an "updated_version" populated over rows with NULLs for "updated_version"? Meaning, you want the most "recent" updated version? (assuming there will be many versions/rows in the table for a given employee_id).

Try (untested):

select * from (
  select e.*, row_number() over (partition by employee_id order by updated_version desc nulls last) rnum
  from employee e
  where employee_id = '1234'
)
where rnum = 1;

The partition by isn't strictly needed as you're looking for just 1 employee_id, but will be needed if you want to expand this to multiple employee ids.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try this way:

Select * from Employee 
where employee_id =  '1234' 
and (updated_version is null or updated_version='1')

EDIT:

Select * 
from Employee e
left join other_table ot on ot.employee_id = e.employee_id
                         and ot.updated_version =e.updated_version
Improve this answer

7 Comments

If i have to pull updated_version = '1' , I will get both the records having null as well as version with 1. I should get only 1 record having version as 1 :(
The value will be coming from other table. If an employee having more than one updated_version, we will get last version. If not we will receive it as null
@muthukumar Try new solution I've added please
That didn't work either. As the value will be null, we cannot search with = null..
@muthukumar Can you show as sample data and expected result or if you can sql fiddle would the best option
|
1

Try this query out:

Select * from Employee where employee_id = '1234' and (updated_version ='1' OR updated_version is null)

EDIT: Try this (Untested)!

Select * from Employee where employee_id = '1234' and 
CASE updated_version
WHEN '1' THEN '1' 
WHEN Null THEN Null 
ELSE updated_Version
END
Improve this answer

3 Comments

As i said, it will return 2 rows if the udpated_version is 1. I should get only one row with version = 1.
Yeah put everything you'd expect to use in the end in your original question. Also, try the new query.
I have tried the new query.. Remember the order version is not necessarily 1.. Its just the sample data.. I have to query the Employee table for a particular Employee_id and updated_version. I 'll receive the value from the other table. What ever value i received it, When i query in Employee table, I should get rows only for that particular condition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.