Required Query to get specific output

Question

I have a view which has structure like below
CountryCode               TempRegistrationID                PermRegistrationID                   Deleted
IND                               0001-07730/00                                                                            N
USA                               835094326035007                  0835-0943260-35-007                N
NWZ                              H111200                                                                                      N

Every country code will have tempRegistrationID, PerRegistrationID. I want to display RegistrationID which will be depend on following condition:

1. If user is having both  tempRegistrationID, PerRegistrationID then it will show  PerRegistrationID As  RegistrationID

2. otherwise if it is having tempRegistrationId and not PerRegistrationID, then tempRegistrationId will be its RegistrationID.
This RegistrationId is temporary column where I want to display this values, its not present in my existing View.

I am not able to get this resultset using case structure or by any other option.I want result as shown below. Can you please suggest the way to do this?"     

RegistrationID
0001-07730/00
0835-0943260-35-007
H111200

I am trying query as below:

select
r.PermRegistrationID,
r.TempRegistrationID,
case when (ISNULL(r.PermRegistrationID,'') ='') then r.TempRegistrationID
 else r.TempRegistrationID  end  as RegistrationID
from myView r
where r.CountryCode IN( 'IND','NWZ','USA' ) and r.Deleted ='N'

Answer 1

If I understood correctly , look at COALESCE system function in the BOL.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Use Case When in your select query appropriately.

Create Table T1 (Code varchar(3),TempRegID Varchar(100),PermRegID Varchar(100))
Insert into T1 Values('IND','0001-07730/00',NULL)
Insert into T1 Values('USA','835094326035007','0835-0943260-35-007')
Insert into T1 Values('NWZ','H111200','')
Select Code,Case When LEN(LTRIM(RTRIM(PermRegID)))>0 Then PermRegID Else TempRegID End as RegID
From T1

EDIT: Added code snippet.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

I have a view which has structure like below
CountryCode               TempRegistrationID                PermRegistrationID                   Deleted
IND                               0001-07730/00                                                                            N
USA                               835094326035007                  0835-0943260-35-007                N
NWZ                              H111200                                                                                      N

Every country code will have tempRegistrationID, PerRegistrationID. I want to display RegistrationID which will be depend on following condition:

1. If user is having both  tempRegistrationID, PerRegistrationID then it will show  PerRegistrationID As  RegistrationID

2. otherwise if it is having tempRegistrationId and not PerRegistrationID, then tempRegistrationId will be its RegistrationID.
This RegistrationId is temporary column where I want to display this values, its not present in my existing View.

I am not able to get this resultset using case structure or by any other option.I want result as shown below. Can you please suggest the way to do this?"     

RegistrationID
0001-07730/00
0835-0943260-35-007
H111200

I am trying query as below:

select
r.PermRegistrationID,
r.TempRegistrationID,
case when (ISNULL(r.PermRegistrationID,'') ='') then r.TempRegistrationID
 else r.TempRegistrationID  end  as RegistrationID
from myView r
where r.CountryCode IN( 'IND','NWZ','USA' ) and r.Deleted ='N'

What if first expression is empty string

Answer 4

Then you can REPLACE it with NULL (REPLACE(col,'',NULL)) or check in CASE EXPRESSION for >''.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing