topic Re: sql query not working in QlikView

sql query not working

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts,

I am trying to fetch the data from SAP but it gives me Error

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Comma without preceding colon (after OPEN ?).)

SQL Select MANDT OBJNR STAT INACT CHGNR from JEST WHERE OBJNR IN (select OBJNR from EQUI WHERE EQTYP = 'C') AND [INACT] <> 'X' AND MATCH(STAT,'I0100')

GROUP BY [OBJNR];

below id my table :

[JEST]: // Individual Object Status

Load

//MANDT & OBJNR & STAT AS %KEY_JEST_MANDT_OBJNR_STAT,

[MANDT] as [Client_MANDT],

[OBJNR] as [Object number_OBJNR],

[STAT] as [Status_STAT],

[INACT] as [Status Inact._INACT],

[CHGNR] as [Change number_CHGNR];

SQL Select MANDT OBJNR STAT INACT CHGNR from JEST WHERE OBJNR IN (select OBJNR from EQUI WHERE EQTYP = 'C') and [INACT] <> 'X' AND MATCH([STAT],'I0100') GROUP BY [OBJNR];

STORE * FROM [JEST] INTO E:\demo operation generator\JEST.QVD;

DROP TABLE [JEST];

Can someone let me know how to solve it.

thanx.

Re: sql query not working

prma7799 — Fri, 29 Dec 2017 11:08:15 GMT

Comma is missing

Try this

SQL Select MANDT ,OBJNR ,STAT, INACT, CHGNR from JEST WHERE OBJNR IN (select OBJNR from EQUI WHERE EQTYP = 'C') and [INACT] <> 'X' AND MATCH([STAT],'I0100') GROUP BY [OBJNR];

Re: sql query not working

martinpohl — Fri, 29 Dec 2017 11:15:12 GMT

I'm not sure that sap SQL knows match. use and STAT = '0100'

and not INACT = 'X' instead <> 'X'

Bytheway: in big tables like JEST it could be faster to select all datas from SAP and reduce in Qlik when the fields you compare are not with search index

Re: sql query not working

martinpohl — Fri, 29 Dec 2017 11:16:05 GMT

SAP select don't need comma as field seperator

Re: sql query not working

prma7799 — Fri, 29 Dec 2017 11:20:14 GMT

Ok ...

I don't know about SAP..

Re: sql query not working

Anonymous — Sat, 30 Dec 2017 05:50:10 GMT

hi martin

I'm not sure that sap SQL knows match. use and STAT = '0100'

and not INACT = 'X' instead <> 'X'

it is not working .it gives error:

QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Forbidden words used)

SQL SQL Select MANDT OBJNR STAT INACT CHGNR from JEST WHERE NOT [INACT] = 'X' AND [STAT] = 'I0100' AND OBJNR IN (select OBJNR from EQUI WHERE EQTYP = 'C')

GROUP BY [OBJNR]

Re: sql query not working

martinpohl — Sat, 30 Dec 2017 12:51:52 GMT

why you are using the group by-statement?

Re: sql query not working

martinpohl — Sat, 30 Dec 2017 13:35:25 GMT

check this

http://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/6.2/QV-connection/SAP-SQL-connection-Subselect.htm

your Statement is missing the subselect