Ошибка incorrect syntax near the keyword from

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?

Я пишу 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;

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..