Sql ошибка 156

May 28, 2013 by Muhammad Imran

UNION operator is one of the most frequent operators used in SQL Server scripting. As per MSDN  it is used to combine the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union . But there is an issue with Union operator which prohibits order by for each table separately while using UNION / UNION ALL. It generates an error.

Let me explain this error in detail :

Message Number: 156

Severity : 15

Error Message: Incorrect syntax near the keyword ‘UNION’.

Error Generation:

Let me create a sample to demonstrate this error.

USE tempdb
GO

CREATE TABLE tbl_Sample1
(
 [ID] INT,
 [Grade] VARCHAR(50)
)
GO
CREATE TABLE tbl_Sample2
(
 [ID] INT,
 [Grade] VARCHAR(50)
)

INSERT INTO tbl_Sample1 VALUES (1,'Grade A')
INSERT INTO tbl_Sample1 VALUES (2,'Grade B')
INSERT INTO tbl_Sample1 VALUES (3,'Grade C')
INSERT INTO tbl_Sample1 VALUES (4,'Grade D')
INSERT INTO tbl_Sample1 VALUES (5,'Grade E')
INSERT INTO tbl_Sample1 VALUES (6,'Grade F')
GO
INSERT INTO tbl_Sample2 VALUES (1,'1st')
INSERT INTO tbl_Sample2 VALUES (2,'2nd')
INSERT INTO tbl_Sample2 VALUES (3,'3rd')
INSERT INTO tbl_Sample2 VALUES (4,'4th')
INSERT INTO tbl_Sample2 VALUES (5,'5th')

Once you created the above sample, lets make a UNION ALL query and try to ORDER BY each table separately.

USE tempdb
GO
SELECT [ID],[Grade]
FROM tbl_Sample1
Order By [ID]

UNION ALL

SELECT [ID],[Grade]
FROM tbl_Sample2
Order By [ID]

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘UNION’.

Ooopps…… I am unable to execute it.

Resolution:
It is very simple to resolve, just add one more column with any sorting number… either 1,2,3 or A,B,C and order on the basis of this column. Let me demonstrate it.

USE tempdb
GO
SELECT
		  'A' AS [Order ID]
		, [ID]
		, [Grade]
FROM tbl_Sample1

UNION ALL

SELECT
		  'B' AS [Order ID]
		, [ID]
		, [Grade]
FROM tbl_Sample2
Order By [Order ID]
--OUTPUT

Conclusion :

Remember, whenever you use UNION / UNION ALL and have to order by each table separately, just add one more column with any sorting value and order by on the basis of this column.

Проблемы

Предположим, что вы используете Microsoft SQL Server 2016 и 2017 изменить сбор данных (CDC) для Oracle с помощью Attunity, чтобы реплицировать изменения данных из баз данных Oracle в базы данных SQL Server. Таблица в Oracle содержит столбец с именем «KEY», а этот столбец также является первичным ключом таблицы. В этой ситуации при использовании конструктора изменений данных для Oracle с помощью Attunity, чтобы добавить эту таблицу для изменений записи, может возникнуть сообщение об ошибке, подобное приведенному ниже.

Неправильный синтаксис около ключевого слова «KEY».

Неправильный синтаксис около ключевого слова WITH. Если этот оператор является общим табличным выражением, предложением XMLNAMESPACES или предложением контекста отслеживания изменений, Предыдущая инструкция должна заканчиваться точкой с запятой.

System. Data. SqlClient. SqlException (0x80131904): неправильный синтаксис около ключевого слова «KEY».

Неправильный синтаксис около ключевого слова WITH. Если этот оператор является общим табличным выражением, предложением XMLNAMESPACES или предложением контекста отслеживания изменений, Предыдущая инструкция должна заканчиваться точкой с запятой.

на странице Attunity. SqlServer. XdbCdcConfig. Core. Managers. CDCManagerImp. ExecuteBatchSqlInTransaction (строка dbName, IList ‘ 1 sqlCommand)

на странице Attunity. SqlServer. XdbCdcConfig. Core. Managers. CDCManagerImp. UpdateCaptureTables (CDCInstanceInfo cdcInfoForUpdate)

на странице Attunity. SqlServer. XdbCdcDesigner. UI. EditCDCInstanceForm. UpdateCaptureTables ()

ClientConnectionId:ConnectionID

Номер ошибки: 156, состояние: 1, класс: 15 

Решение

Эта проблема исправлена в перечисленных ниже накопительных обновлениях для SQL Server.

       Накопительное обновление 6 для SQL Server 2017

       Накопительное обновление 8 для SQL Server 2016 с пакетом обновления 1 (SP1)

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Ссылки

Ознакомьтесь с терминологией, которую корпорация Майкрософт использует для описания обновлений программного обеспечения.

В этой статье упомянуты программные продукты независимых производителей. Корпорация Майкрософт не дает никаких гарантий, подразумеваемых и прочих, относительно производительности и надежности этих продуктов.

You cannot mix the simple and searched types of CASE expression. Remove the field name DMProject.dbo.TypeFourTraining.Column2 specified after the CASE.

Correct syntax for your query:

SELECT  CASE 
            WHEN Column2 between 181    AND 360 THEN '181-360' 
            WHEN Column2 between 0      AND 180 THEN '0-180' 
        END as score 
FROM    DMProject.dbo.TypeFourTraining 

Two types of CASE expressions:

There are two types of CASE expression namely Simple and Searched. You cannot combine Simple and Searched in the same expression.

Simple CASE:

CASE input
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    ELSE ''
END

Searched CASE with simple example:

CASE 
    WHEN input = 1 THEN 'a'
    WHEN input = 2 THEN 'b'
    WHEN input = 3 THEN 'c'
    ELSE ''
END

Searched CASE with slightly complex example:

This involves multiple columns. You can add multiple columns in each of the WHEN statements.

CASE 
    WHEN input = 1 AND second_column = 2 THEN 'a'
    WHEN input = 2 AND third_column  = 3 THEN 'b'
    WHEN input = 3 AND (second_column = 4 OR third_column = 6) THEN 'c'
    ELSE ''
END

Getting an error, I’m sure it’s syntax related.

DECLARE @Rank AS INT = 0;

SELECT
    asn_key,
    asn_code,
    asn_name, 
    asn_eweb_description_ext,
    @Rank = CASE 
               WHEN asn_name = 'AONE' THEN 1
               WHEN asn_name = 'ACHI' THEN 1
               WHEN asn_name = 'ATLARGE' THEN 1
               WHEN asn_name = 'IFD' THEN 1
               ELSE 0 
            END AS ASN_Rank
FROM
    mb_association
JOIN
    mb_association_ext (nolock) ON asn_key = asn_key_ext
WHERE
    asn_eweb_description_ext IS NOT NULL
ORDER BY 
    ASN_Rank ASC

I need to «group» my associations. If its not one of those associations listed then it needs to be listed first in ASC order else I need it to be listed lastly in ASC order. It looks like its telling me I have a syntax error but I’m not sure why its giving me the error. I don’t see any issues with the code and I’ve reviewed similar code.

Edit:
I tried to approach it a different direction but got another error. I’m not very good with group by clause.

New Code:

select asn_key, asn_code, asn_name,  asn_eweb_description_ext
from mb_association join
     mb_association_ext (nolock)
     on asn_key = asn_key_ext
where asn_eweb_description_ext is not null
--order by (CASE WHEN asn_name in ('AONE', 'ACHI', 'ATLARGE', 'IFD') then 1 else 0 end);
GROUP BY (CASE WHEN asn_name in ('AONE', 'ACHI', 'ATLARGE', 'IFD') then 1 else 0 end);

Error:
Msg 8120, Level 16, State 1, Line 1
Column ‘mb_association.asn_key’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Error 156

Severity Level 15

Message Text

Incorrect syntax near the keyword '%.*ls'.

Explanation

This error indicates that the syntax of a Transact-SQL statement is incorrect and that the syntax error was detected near the keyword specified in the error message. The most frequent causes for syntax errors are misspellings of Transact-SQL keywords or operators, and specifying the syntax of a Transact-SQL statement in the wrong order.

One of the more complicated causes for this error may be a compatibility level mismatch for the current database. If the current database has a compatibility level other than 70, Microsoft® SQL Server™ will not recognize any of the keywords that a database with a compatibility level of 70 would recognize.

Action

First, check the Transact-SQL statement syntax near the keyword specified in the error message. Because Transact-SQL language syntax can be very complex, SQL Server may incorrectly report the position of the syntax error as later in the Transact-SQL statement than it actually occurred. Second, reexamine the entire Transact-SQL statement that generated the error. Verify the syntax order of the statement.

Ensure that the database does not have a compatibility level of 65 and has a compatibility level of 70.

See Also

Backward Compatibility

Errors 1 — 999

Transact-SQL Overview

Reserved Keywords

sp_dbcmptlevel

Hi all,

I got an error message 156, when I executed the following code:

////—SQLQueryParent&Child.sql—////////

Use newDB

GO

—-Creating dbo.Person as a Parent Table—-

CREATE TABLE dbo.Person

(PersonID int PRIMARY KEY NOT NULL,

FirstName varchar(25) NOT NULL,

LastName varchar(25) NOT NULL,

City varchar(25) NOT NULL,

State varchar(25) NOT NULL,

Phone varchar(25) NOT NULL)

INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)

SELECT 1, «George», «Washington», «Washington», «DC», «1-000-1234567»

UNION ALL

SELECT 2, «Abe», «Lincoln», «Chicago», «IL», «1-111-2223333»

UNION ALL

SELECT 3, «Thomas», «Jefferson», «Charlottesville», «VA», «1-222-4445555»

GO

—-Creating dbo.Book as a Child table—-

CREATE TABLE dbo.Book

(BookID int PRIMARY KEY NOT NULL,

BookTitle varchar(25) NOT NULL,

AuthorID int FOREIGN KEY NOT NULL)

INSERT dbo.Book (BookID, BookTitle, AuthorID)

SELECT 1, «How to Chop a Cherry Tree», 1

UNION ALL

SELECT 2, «Valley Forge Snow Angels», 1

UNION ALL

SELECT 3, «Marsha and ME», 1

UNION ALL

SELECT 4, «Summer Job Surveying Viginia», 1

UNION ALL

SELECT 5, «Log Chopping in Illinois», 2

UNION ALL

SELECT 6, «Registry of Visitors to the White House», 2

UNION ALL

SELECT 7, «My Favorite Inventions», 3

UNION ALL

SELECT 8, «More Favorite Inventions», 3

UNION ALL

SELECT 9, «Inventions for Which the World is Not Ready», 3

UNION ALL

SELECT 10, «The Path to the White House», 2

UNION ALL

SELECT 11, «Why I Do not Believe in Polls», 2

UNION ALL

SELECT 12, «Doing the Right Thing is Hard», 2

GO

—Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table

SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID

GO

////—Results—//////

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword ‘NOT’.

////////////////////////////////////////////////////

(1)  Where did I do wrong and cause the Error Message 156?

(2)  I try to get  a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword ‘NOT’.

Can I get a Parent-Child table after the error 156 is resolved? 

Please help and advise.

Thanks,

Scott  Chang