Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help

Author

Topic

Chester 1
Starting Member

3 Posts

Posted - 2013-04-16 : 12:30:57

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-16 : 13:26:22

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-16 : 13:28:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 01:11:50

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

Chester 1
Starting Member

3 Posts

Posted - 2013-04-19 : 07:07:47

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 08:53:59

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.

Chester 1
Starting Member

3 Posts

Posted - 2013-04-19 : 11:35:35

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 12:17:52

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-20 : 02:44:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources