Syntax Error in Adapter Query

book

Article ID: 110036

calendar_today

Updated On:

Products

OBLICORE GUARANTEE

Issue/Introduction

Query in Adapter prompts a syntax error message

Error in Fill Data of "NewQuery" query. Caught COM ERROR in operation QUERY (select ticket_no, start_time, end_time,ticket_category from 
(select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS S_No, ref_num ticket_no, dateadd(ss,time_stamp,'1970-01-01 05:30:00') as start_time, status, 
(select substring(sym,0,charindex('.',sym)) from prob_ctg where persid=c.category) as service, 
(select sym from prob_ctg where c.category = prob_ctg.persid )as ticket_category 
from [mdb].[dbo].call_req c , [mdb].[dbo].act_log a, [mdb].[dbo].prob_ctg 
where c.persid = a.call_req_id and c.category = prob_ctg.persid and a.type in ('INIT','SLARESUME') and status='CL'and prob_ctg.sym = 'RSI.DARPAN RSI.New.ClientApp.Deployment' 
) a, 
(select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS S_No2, ref_num r2,dateadd(ss,time_stamp,'1970-01-01 05:30:00') as end_time, 
(select substring(sym,0,charindex('.',sym)) from prob_ctg where persid=c.category) as service2, 
(select sym from prob_ctg where c.category = prob_ctg.persid )as ticket_category2 from [mdb].[dbo].call_req c , [mdb].[dbo].act_log a, [mdb].[dbo].prob_ctg 
where c.persid = a.call_req_id and c.category = prob_ctg.persid and a.type in ('SLADELAY','CLREQ') and status='CL' and prob_ctg.sym = 'RSI.DARPAN RSI.New.ClientApp.Deployment' ) b 
where a.S_No = b.S_No2 
order by ticket_no, start_time, end_time and ( (ticket_no>'TKT0000000') or (ticket_no = 'TKT0000000' and start_time>CONVERT(datetime,'07/07/1970 00:00:00',104)) ) order by ticket_no ,start_time ) [[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'and'. (HR: 0x80040e14) (IDispatch error #3092) (Source = Microsoft OLE DB Provider for ODBC Drivers)][(Error #80040e14) (Source = Microsoft OLE DB Provider for ODBC Drivers) (Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'and'.) (SQLState = 37000) (NativeError: 9c)]

Cause

This is caused by the Data Source Interface default setting

Environment

CA Business Service Insight 8.3.5

Resolution

Please follow below steps to resolve this issue

1. Login to Business Service Insight (BSI) as Administrator
2. Click on Design drop down - Data Acquisition - Adapters
3. Open the Adapter that causing the error message and Click Edit

<Please see attached file for image>

User-added image

4. Got to "Data Source Interface" step of the adapter

<Please see attached file for image>

User-added image

5. Click on "Advanced" button section

<Please see attached file for image>

User-added image

6. Uncheck the "Is AutoComplete" check box.

<Please see attached file for image>

User-added image

7. Save the adapter and run the query

Note: BSI won't use the key field on the query if the "Is AutoComplete" check box is unchecked. 

Attachments

1558697852547000110036_sktwi1f5rjvs16j3u.jpeg get_app
1558697850881000110036_sktwi1f5rjvs16j3t.jpeg get_app
1558697849027000110036_sktwi1f5rjvs16j3s.jpeg get_app
1558697847250000110036_sktwi1f5rjvs16j3r.jpeg get_app