SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chester 1
Starting Member

3 Posts

Posted - 04/16/2013 :  12:30:57  Show Profile  Reply with Quote
Hi

I need help with an T-SQL formula that will move address details along if one of the address fields is blank within the report. For Example:

If address line 1 is blank then insert address line 2 else address line 1
If address line 2 is blank or address line 2 is address line 1 then address line 3.

Any help would be much appriciated it. I am new to T-SQL codeing but seem to be using it alot in line with the HR System that i am using at the moment.

Thanks


Edited by - Chester 1 on 04/16/2013 12:32:16

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/16/2013 :  13:26:22  Show Profile  Reply with Quote
Are addressline1, addressline2 etc. columns in a table? If so, you can do the following:
SELECT
   COALESCE(NULLIF(addresline1,''),NULLIF(addressline2,''),NULLIF(addressline3,''))
FROM
   TheTable
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/16/2013 :  13:28:41  Show Profile  Reply with Quote
I think you are looking for the COALESCE function, but if that is not what you want, please psot sample data and expected ouptut:
DECLARE @Foo TABLE 
(
	Address1 VARCHAR(50)
	,Address2 VARCHAR(50)
	,Address3 VARCHAR(50)
)

INSERT @Foo VALUES
('A1', 'A2', 'A3'),
('A1', NULL, NULL),
(NULL, 'A2', 'A3'),
(NULL, NULL, 'A3')

SELECT COALESCE(Address1, Address2, Address3) AS Address
FROM @Foo
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/17/2013 :  01:11:50  Show Profile  Reply with Quote
sounds like this to me

SELECT
   COALESCE(NULLIF(addresline1,''),NULLIF(addressline2,'')) AS Address1,
   CASE WHEN NULLIF(addresline1,'') IS NULL OR  NULLIF(addresline2,'') IS NULL THEN COALESCE(NULLIF(addressline3,'')) ELSE  NULLIF(addresline2,'') IS NULL END AS Address2
FROM
   TheTable


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Chester 1
Starting Member

3 Posts

Posted - 04/19/2013 :  07:07:47  Show Profile  Reply with Quote
Hi

Thanks for you help above. I have tried the formula and it is not working.

I am not sure if this will help but, the HR System that I am using Cascade. The query is created in the systems query builder and I need each address field to show in a separate column once the report is exported to excel.

Anymore suggestions?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/19/2013 :  08:53:59  Show Profile  Reply with Quote
quote:
Originally posted by Chester 1

Hi

Thanks for you help above. I have tried the formula and it is not working.

I am not sure if this will help but, the HR System that I am using Cascade. The query is created in the systems query builder and I need each address field to show in a separate column once the report is exported to excel.

Anymore suggestions?



What did it do when you tried? Is it giving you an error message, or giving incorrect data, or something else?

I am not familiar with Cascade, so I can't help there. I inferred you were working with T-SQL based on your original posting. If Cascade is using T-SQL behind the scenes, then the queries any of us posted should work even if they don't give you the exact formatting/results you are looking for.
Go to Top of Page

Chester 1
Starting Member

3 Posts

Posted - 04/19/2013 :  11:35:35  Show Profile  Reply with Quote
It gives an error message - incorrect formula. I entered the formula in single formula field within the query builder. Would it help if i gave a screen shot of how the formula displays in the query builder?

I am really knew to all this so please bear with me. It’s a massive learning curve.

I really appreciate your help and suggestions.

Becky
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/19/2013 :  12:17:52  Show Profile  Reply with Quote
You may get better and more accurate answers if you asked in a support forum for the software, if there is one.

Do you know if it uses a SQL Server backend, or does it have its own database, or is it using another RDBMS like Oracle? If it uses SQL Server, the queries posted should have worked. That it did not work probably indicates that Cascade supports only a subset of the T-SQL syntax.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/20/2013 :  02:44:00  Show Profile  Reply with Quote
Looking at error message it doesnt seem to be a native T-SQL one. So you may have to post this in relevant forums.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000