I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long.  I will call them PRIMARY & ALTERNATE for this discussion.  I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.

I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.

SELECT    TXPRCL, TXALTR, TXANAM
FROM    dbo.PCWEBF21
WHERE    (TXALTR > 0)
UNION
SELECT    TXPRCL, TXTAXP, TXTNAM
FROM    dbo.PCWEBF21
WHERE    TXALTR = 0

Would a Select Case work? something like

Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.

Thanks, Jim

Yes, you can use CASE statements, e.g.

select case when Alternate = 0 or Alternate IS NULL then PrimaryAddress1 else AlternateAddress1 end as Address1, ...

thanks for the reply, Naomi.  Since this is my first attempt at designing a query in the Server Management Studio, I do not know where the error is in this syntax.  I am using only two fields for simplicity.

Help please.

SELECT CASE
    WHEN TXALTR = 0 then TXTAXP
    ELSE TXALTR
END
FROM dbo.PCWEBF21

What is the exact error? Do both columns TXTAXP and TXALTR have the same type?

The query looks OK, but make sure you correctly specified the column names (and table name) and that the types of the columns match each other.

Error is:

Error in SELECT clause: expression near 'TXALTR'.
Unable to parse query text.

My expression is:

SELECT CASE
    WHEN TXALTR = 0 then TXTAXP
    ELSE TXALTR
END
FROM dbo.PCWEBF21

Table and Fields are:

PCWEBF21 is a dbo table so it is dbo.PCWEBF21

TXTAXP, TXALTR are both numeric.

What tool you're using to write the query? Also, please provide the SQL Server version 

SELECT @@VERSION.

I Apologize for not knowing what you are asking.  I right click the View folder in the database and select NEW VIEW.  Then I use the code box to enter the query syntax.  I should mention that i am creating this in an SDE to use with ArcGIS.  We usually create the code in Server Management Studio then paste in the code box withing ArcGIS.  It works perfectly for simple select queries.

SQL Server 2008 R2

Microsoft SQL Server Management Studio                        10.50.4000.0
Microsoft Analysis Services Client Tools                        10.50.4000.0
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.11.9600.17959
Microsoft .NET Framework                        2.0.50727.5485
Operating System                        6.1.7601

I've tried in SQL Server 2014 and it worked fine for me. However, I suggest to not use Query designer for creating views. Just open a new query, type

create view dbo.myViewName 

AS

SELECT ...

and it should work fine. It is possible that there are bugs in the designer.

i just tried ignoring the error and i appear to get a table back with the field.  Now it errors when i try to add fields.  When you used as Address1, ... after the End part, is that for adding fields?

It is for specifying the alias (name) for this new computed column.

I think you'll spend more time struggling with the designer than it would take you to create the statement in the new query window.

Don't use the query designer or you will keep fighting these battles all the time. Type your queries instead.

Naomi, After ignoring the error message i ran it OK.  Then  i pasted the code into ArcGIS for a new Query Layer and it seems to work.

SELECT CASE
    WHEN TXALTR = 0 then TXTAXP
    ELSE TXALTR
END
AS Taxpayer_Number, TXPRCL, TXTAXP, TXTNAM as [Primary Name], TXALTR, TXANAM as [Alternate Name]
FROM gisadmin.PCWEBF21

thank you very much, it must have been trying to teach me the basic syntax.  Now if you could tell me where to find other types of statements other than the SELECT and SELECT CASE i have just done.

Jim

Tibor, i do not know enough to type the queries so i needed this forum to figure it out.  the next one will be easier i am sure.

thanks again.

• Edited by GIS Jim 11 hours 53 minutes ago

I suggest you pick up a beginners book on the SQL language, or possible attend a class. Well spent time. :-)

Well, Transact-SQL is fairly simple and intuitive language. You may want to start learning it from http://www.w3schools.com/sql/

Master the basic stuff first, then you will be able to write more advanced queries. It will all come with practice.

Naomi, where you have written "then PrimaryAddress1 else AlternateAddress1", how do i get all five of the Primary or Alternate fields? It does not work to type "Then Name, Address1, Address2, City, State, Zip".  Would you suggest a new thread for this?

There are lot of free e-books that can be download. There is a list at Microsoft TechNet WIKI, in this page:

http://social.technet.microsoft.com/wiki/contents/articles/29916.sql-and-transact-sql-free-e-books.aspx

* If you know about any FREE e-books that you have read and you recommend, you can add it to the list, as

You need a separate CASE statement for each of your columns, e.g.

case when AlternateAddress1 ='' OR AlternateAddress1 IS NULL then PrimaryAddress1 else

AlternateAddress1 end as Address1,

case when AlternateAddress2 ='' or AlternateAddress2 IS NULL then PrimaryAddress2 else

AlternateAddress2 end as Address2, etc.

You can also simplify check for NULL and '' by using NULLIF function,e.g.

case when NULLIF(AlternativeAddress1,'') IS NULL then ...

Or you can use opposite logic using COALESCE function.

I suggest to start investing some time into learning the basics of Transact-SQL.