When I execute the following code:
USE Database
GO
DECLARE @DF Datetime = CONVERT(datetime,'30/01/2017',103)
DECLARE @DT Datetime = CONVERT(datetime,'13/03/2017',103)
DECLARE @Owner nvarchar(255) = 'user@email.co.uk'
DECLARE @Box nvarchar(255) = 'Sent Items'
DECLARE @ResultTo int = 0
DECLARE @ResultCC int = 0
DECLARE @ResultBCC int = 0
SET @ResultTo = COUNT(ToEmailAddress) FROM EmailTo
WHERE fkDataExtractTableID IN
(
SELECT DataExtractTableID
FROM [Database].[dbo].[DataExtractTable]
WHERE (DateSent between @DF AND @DT)
AND ([Owner] = @Owner)
AND (Box = @Box)
)
I get this error message:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword ‘FROM’.
What am I missing?
marc_s
721k173 gold badges1320 silver badges1442 bronze badges
asked Oct 16, 2017 at 0:05
2
I think your sql wrong on line 12. It may be like this:
SET @ResultTo = (select COUNT(ToEmailAddress) FROM EmailTo)
answered Oct 16, 2017 at 0:37
I Love YouI Love You
2582 silver badges8 bronze badges
1
Change your variable assignment to:
select @ResultTo = count(ToEmailAddress) from EmailTo
answered Oct 16, 2017 at 0:51
thomasthomas
2,5921 gold badge28 silver badges44 bronze badges
1
You can’t use 2 different syntax in a select query. Either use Set statement or use simple query
Set Statement — where you set a single variable as a return from a select query
SET @ResultTo = (Select COUNT(ToEmailAddress) FROM EmailTo
WHERE fkDataExtractTableID IN
(
SELECT DataExtractTableID
FROM [Database].[dbo].[DataExtractTable]
WHERE (DateSent between @DF AND @DT)
AND ([Owner] = @Owner)
AND (Box = @Box)
) )
Or Select statement where you set multiple variables value like
Select @ResultTo = COUNT(ToEmailAddress)
--Aslo define other variable, if you want
-- like @ResultTo1 , but this query is aggregation, so only aggeregate function use
FROM EmailTo
WHERE fkDataExtractTableID IN
(
SELECT DataExtractTableID
FROM [Database].[dbo].[DataExtractTable]
WHERE (DateSent between @DF AND @DT)
AND ([Owner] = @Owner)
AND (Box = @Box)
)
answered Oct 16, 2017 at 4:54
![]()
Ajay2707Ajay2707
5,6686 gold badges39 silver badges58 bronze badges
Below is the corrected one:
DECLARE @DF Datetime = CONVERT(datetime,'30/01/2017',103)
DECLARE @DT Datetime = CONVERT(datetime,'13/03/2017',103)
DECLARE @Owner nvarchar(255) = 'user@email.co.uk'
DECLARE @Box nvarchar(255) = 'Sent Items'
Declare @ResultTo int = 0
Declare @ResultCC int = 0
Declare @ResultBCC int = 0
SELECT @ResultTo = COUNT(ToEmailAddress) FROM EmailTo
WHERE fkDataExtractTableID IN
(
SELECT DataExtractTableID
FROM [Database].[dbo].[DataExtractTable]
WHERE (DateSent between @DF AND @DT)
AND ([Owner] = @Owner)
AND (Box = @Box)
)
answered Oct 16, 2017 at 2:35
2
User1107959781 posted
Hi, I have Error when I write this SQL statement retrieving the non selected facilitator for a specific course:
The SQL statement:<?xml:namespace prefix = o ns = «urn:schemas-microsoft-com:office:office» /><o:p></o:p>
<o:p> </o:p>
SELECT facilitators.facilitator_id, facilitators.facilitator_name<o:p></o:p>
FROM
facilitators INNER JOIN<o:p></o:p>
[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id<o:p></o:p>
WHERE (NOT (facilitators.facilitator_id IN<o:p></o:p>
(SELECT [transaction_ facilitators].trans_Facilitator_id<o:p></o:p>
FROM
[transaction_ facilitators]<o:p></o:p>
WHERE [transaction_ facilitators].trans_Course_id = 2)))<o:p></o:p>
GROUP BY facilitators.facilitator_id, facilitators.facilitator_name<o:p></o:p>
ORDER BY facilitators.facilitator_id<o:p></o:p>
<o:p> </o:p>
Every thing goes right until I change this:<o:p></o:p>
WHERE [transaction_ facilitators].trans_Course_id = 2<o:p></o:p>
To<o:p></o:p>
WHERE [transaction_ facilitators].trans_Course_id = @course_id<o:p></o:p>
<o:p> </o:p>
It gives me this error:<o:p></o:p>
<o:p> </o:p>
Incorrect syntax near the keyword «From»<o:p></o:p>
<o:p> </o:p>
Any suggestion ??<o:p></o:p>
<o:p> </o:p>
I have a code that is supposed to create an XML output file. I have everything working except getting an error on the from:
SELECT
(SELECT ‘ADD’ as ‘@action-code’,’CLINICAL’ as ‘@data’, ‘OUTPATIENT’ AS ‘@type’, ‘1.0’ as ‘@version’,
(SELECT CONVERT(VARCHAR(10),CAST(GETDATE() AS DATE), 110) AS ‘create-date’,
CONVERT(VARCHAR(5),GETDATE(),108) AS ‘create-time’, ‘xxxxx’ AS ‘create-by’,
‘SQL2008’ as ‘version’, ‘SQL’ as ‘create-by-tool’
FOR XML PATH(‘file-audit-data’),type,ELEMENTS),
(SELECT [provider-id],[npi]
,(SELECT [first-name], [last-name], birthdate, sex, race, ethnic,[postal-code]
,(SELECT ‘ED-THROUGHPUT’ AS ‘@measure-set’
,[encounter-date]
,[arrival-time]
,a.[patient-id]
,(SELECT DISCHGCODE AS ‘@answer-code’, ‘DISCHGCODE’ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
,(SELECT EDDEPARTDT AS ‘@answer-code’, ‘EDDEPARTDT’ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
,(SELECT EDDEPARTTM AS ‘@answer-code’, ‘EDDEPARTTM’ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
,(SELECT emcode AS ‘@answer-code’, ‘EMCODE’ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
,(SELECT PRINDX AS ‘@answer-code’, ‘PRINDX ‘ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
,(SELECT pmtsrce AS ‘@answer-code’, ‘PMTSRCE’ as ‘@question-cd’, ‘0’ as ‘@row-number’
FROM POP2 detail
WHERE detail.[patient-id] = POP2.[patient-id]
FOR XML PATH(‘detail’),type)
—> FROM POP2 x <— this is where the error stops
WHERE x.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
FOR XML PATH(‘encounter’), type, ELEMENTS)
FROM [mhtest].[dbo].[vw_RPT_ADHOC_DATA_DUMP]z
WHERE z.[n_PATIENT_ID] = [mhtest].[dbo].[pop2].[patient-id]
FOR XML PATH(‘patient’), type, ELEMENTS)
FROM [mhtest].[dbo].[pop2] z
WHERE z.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
FOR XML PATH(‘provider’), type, ELEMENTS)—End Provider —
FROM [mhtest].[dbo].[pop2] y
WHERE y.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
ORDER BY NEWID()
FOR XML PATH(‘submission’), type, ELEMENTS)OP18
INTO TOP18z — temp xml table—
Any thoughts as to why this would be?

- Remove From My Forums
-
Question
-
Hi all,
i am trying to run this sql but its giving me the error .
Incorrect syntax near the keyword ‘from’.the query is suppose to return 1 or 0 depending on the condition.
Regards
-
Edited by
sva0008
Thursday, March 4, 2010 7:22 PM
-
Edited by
Answers
-
You are missing the END for the CASE expression. Also, avoid using reserved keywords for object names, like «User».
CASE WHEN u.[User] IS NULL THEN 0 ELSE 1 END
Plamen Ratchev
-
Marked as answer by
sva0008
Thursday, March 4, 2010 7:21 PM
-
Marked as answer by

- Remove From My Forums
-
Question
-
Hi all,
i am trying to run this sql but its giving me the error .
Incorrect syntax near the keyword ‘from’.the query is suppose to return 1 or 0 depending on the condition.
Regards
-
Edited by
sva0008
Thursday, March 4, 2010 7:22 PM
-
Edited by
Answers
-
You are missing the END for the CASE expression. Also, avoid using reserved keywords for object names, like «User».
CASE WHEN u.[User] IS NULL THEN 0 ELSE 1 END
Plamen Ratchev
-
Marked as answer by
sva0008
Thursday, March 4, 2010 7:21 PM
-
Marked as answer by
Я пишу SQL-запрос, но продолжаю получать синтаксическую ошибку:
Incorrect syntax near the keyword ‘GROUP’
Что могло вызвать эту проблему? Я попытался найти все очевидные вещи, но не заметил этого, и я также просмотрел предыдущие сообщения, см. Запрос ниже:
(/****** Script for SelectTopNRows command from SSMS ******/
SELECT ALLGENEDX.[id]
,ALLGENEDX.[Chr]
,ALLGENEDX.[Start]
,ALLGENEDX.[End]
,ALLGENEDX.[ref]
,ALLGENEDX.[alt]
,ALLGENEDX.[Func#refGene]
,ALLGENEDX.[Gene#refGene]
,ALLGENEDX.[Otherinfo]
,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END AS 'PARENT_INFO'
,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END AS 'SIBLING_INFO'
FROM [Exome Data].[dbo].[ALLGENEDX],
(SELECT [ID],
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS PARENT_OTHERINFO,
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[End],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS PARENT_AMMOUNT
FROM [Exome Data].DBO.ALLGENEDX
WHERE ID IN ('1646304GD', '1646307GD'))AS PARENT,
(SELECT [id],
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS SIBLING_OTHERINFO,
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS SIBLING_AMMOUNT
FROM [Exome Data].DBO.ALLGENEDX
WHERE ID IN ('1646310GD', '1646314GD', '1646317GD', '1646320GD', '1646325GD'))AS SIBLING
WHERE SIBLING.[CHR]=ALLGENEDX.Chr
AND SIBLING.[START]=ALLGENEDX.[START]
AND SIBLING.[END]=ALLGENEDX.[END]
AND SIBLING.[REF]=ALLGENEDX.[REF]
AND SIBLING.[ALT]= ALLGENEDX.[ALT]
AND SIBLING.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
AND PARENT.[CHR]=ALLGENEDX.[Chr]
AND PARENT.[START]=ALLGENEDX.[START]
AND PARENT.[END]=ALLGENEDX.[END]
AND PARENT.[REF]=ALLGENEDX.[REF]
AND PARENT.[ALT]= ALLGENEDX.[ALT]
AND PARENT.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
AND ALLGENEDX.ID= '1646299GD' AND ALLGENEDX.Func#refGene = 'exonic'
AND (EXAC_FREQ<'0.005' OR EXAC_FREQ IS NULL)
AND ([1000G_ALL]<'0.005' OR [1000G_ALL] IS NULL)
AND (GNOMAD_EXOME_ALL<'0.005' OR GNOMAD_EXOME_ALL IS NULL)
HAVING CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END > 0
OR CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END > 0
GROUP BY ALLGENEDX.[id]
,ALLGENEDX.[Chr]
,ALLGENEDX.[Start]
,ALLGENEDX.[End]
,ALLGENEDX.[ref]
,ALLGENEDX.[alt]
,ALLGENEDX.[Func#refGene]
,ALLGENEDX.[Gene#refGene]
,ALLGENEDX.[Otherinfo]
,[PARENT_OTHERINFO]
,[SIBLING_OTHERINFO]
ORDER BY len ALLGENEDX.[Chr], Chr, start;/****** Script for SelectTopNRows command from SSMS ******/
SELECT ALLGENEDX.[id]
,ALLGENEDX.[Chr]
,ALLGENEDX.[Start]
,ALLGENEDX.[End]
,ALLGENEDX.[ref]
,ALLGENEDX.[alt]
,ALLGENEDX.[Func#refGene]
,ALLGENEDX.[Gene#refGene]
,ALLGENEDX.[Otherinfo]
,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END AS 'PARENT_INFO'
,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END AS 'SIBLING_INFO'
FROM [Exome Data].[dbo].[ALLGENEDX],
(SELECT [ID],
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS PARENT_OTHERINFO,
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[End],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS PARENT_AMMOUNT
FROM [Exome Data].DBO.ALLGENEDX
WHERE ID IN ('1646304GD', '1646307GD'))AS PARENT,
(SELECT [id],
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS SIBLING_OTHERINFO,
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[end],
[REF],
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS SIBLING_AMMOUNT
FROM [Exome Data].DBO.ALLGENEDX
WHERE ID IN ('1646310GD', '1646314GD', '1646317GD', '1646320GD', '1646325GD'))AS SIBLING
WHERE SIBLING.[CHR]=ALLGENEDX.Chr
AND SIBLING.[START]=ALLGENEDX.[START]
AND SIBLING.[END]=ALLGENEDX.[END]
AND SIBLING.[REF]=ALLGENEDX.[REF]
AND SIBLING.[ALT]= ALLGENEDX.[ALT]
AND SIBLING.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
AND PARENT.[CHR]=ALLGENEDX.[Chr]
AND PARENT.[START]=ALLGENEDX.[START]
AND PARENT.[END]=ALLGENEDX.[END]
AND PARENT.[REF]=ALLGENEDX.[REF]
AND PARENT.[ALT]= ALLGENEDX.[ALT]
AND PARENT.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
AND ALLGENEDX.ID= '1646299GD' AND ALLGENEDX.Func#refGene = 'exonic'
AND (EXAC_FREQ<'0.005' OR EXAC_FREQ IS NULL)
AND ([1000G_ALL]<'0.005' OR [1000G_ALL] IS NULL)
AND (GNOMAD_EXOME_ALL<'0.005' OR GNOMAD_EXOME_ALL IS NULL)
HAVING CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END > 0
OR CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END > 0
GROUP BY ALLGENEDX.[id]
,ALLGENEDX.[Chr]
,ALLGENEDX.[Start]
,ALLGENEDX.[End]
,ALLGENEDX.[ref]
,ALLGENEDX.[alt]
,ALLGENEDX.[Func#refGene]
,ALLGENEDX.[Gene#refGene]
,ALLGENEDX.[Otherinfo]
,[PARENT_OTHERINFO]
,[SIBLING_OTHERINFO]
ORDER BY len ALLGENEDX.[Chr], Chr, start;
|
Rod9i 3 / 3 / 1 Регистрация: 12.05.2016 Сообщений: 17 |
||||
|
1 |
||||
|
15.05.2017, 12:10. Показов 8333. Ответов 1 Метки нет (Все метки)
здравствуйте!
__________________
0 |
|
Даценд
5856 / 4733 / 2940 Регистрация: 20.04.2015 Сообщений: 8,361 |
||||
|
15.05.2017, 13:09 |
2 |
|||
|
РешениеRod9i,
Кстати, в строке 7 такая же ситуация.
3 |
Hi All,
I’m trying to get data from an SQL 2005 server using synchronous JDBC receiver adapter. I’m using PI 7.0. My outgoing message is as follows:
<?xml version=»1.0″ encoding=»UTF-8″ ?>
— http://holding.com/brd/»>
— <STATEMENT>
— <ECZ_IZIN_YUKU action=»SELECT»>
<table>ECZ_IZIN_YUKU</table>
<access />
— <key>
<iy_yil compareOperation=»GT»>1900</iy_yil>
</key>
</ECZ_IZIN_YUKU>
</STATEMENT>
</ns0:MT_IZIN_YUKU_REQ>
I’ve found the following log in the trace file:
#1.5#0014C23BCFC900630000003D000013B000042740B0A858B1#1169058702046#/Applications/ExchangeInfrastructure/AdapterFramework/Services/ADAPTER/ADMIN/JDBC#sap.com/com.sap.aii.af.app#com.sap.aii.adapter.jdbc.XI2JDBC.onInternalMessageBinary(Message)#J2EE_GUEST#0####fa10f5f0a65811db84410014c23bcfc9#XI XI2JDBC[CC_JDBC_RECI_BRD_TO_BHT/HUMANIST/]_2574##0#0#Error#1#com.sap.aii.adapter.jdbc.XI2JDBC#Plain###Error processing request, rollback: Error processing request in sax parser: Error when executing statement for table/stored proc. ‘ECZ_IZIN_YUKU’ (structure ‘STATEMENT’): com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘FROM’.#
Thanks a lot..
Сообщение было отмечено Rod9i как решение