topic Re: SQL SELECT SYNTAX in QlikView

SQL SELECT SYNTAX

Tue, 20 Jun 2017 09:05:58 GMT

Good day,

I have a simple question with probably a simple solution but I keep getting the following script and error:

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "," is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)

SQL Select

LIFNR,

GJAHR,

MONAT,

BLART,

SUM(DMBTR) AS [AMOUNT]

FROM

BSAK

WHERE GJAHR = '2018' AND (BLART = 'RE' OR BLART = 'KR')

GROUP BY LIFNR, GJAHR, MONAT, BLART;

Please assist as I cannot find the problem

Kind regards

Re: SQL SELECT SYNTAX

prma7799 — Tue, 20 Jun 2017 09:16:13 GMT

Query which you write looks ok just check spelling for fields ..

Re: SQL SELECT SYNTAX

prma7799 — Tue, 20 Jun 2017 09:17:31 GMT

Or try to load simple table as below

SQL Select

FROM

BSAK ;

Re: SQL SELECT SYNTAX

Tue, 20 Jun 2017 09:28:44 GMT

Thanks for the reply P M. I think I may have not been clear with my initial query. I get a script error but I do not know why, everything looks fine.

I do not want to select all records since this is a massive SAP table

Script is:

Load *

;

SQL Select

LIFNR,

GJAHR,

MONAT,

BLART,

SUM(DMBTR) AS [AMOUNT]

FROM

BSAK

WHERE GJAHR = '2018' AND (BLART = 'RE' OR BLART = 'KR')

GROUP BY LIFNR, GJAHR, MONAT, BLART;

Regards

Re: SQL SELECT SYNTAX

avinashelite — Tue, 20 Jun 2017 09:32:04 GMT

Did you tried your SQL query in DB once ? try to run the query once in DB and check

SQL Select

LIFNR,

GJAHR,

MONAT,

BLART,

SUM(DMBTR) AS [AMOUNT]

FROM

BSAK

WHERE GJAHR = '2018' AND (BLART = 'RE' OR BLART = 'KR')

GROUP BY LIFNR, GJAHR, MONAT, BLART

Re: SQL SELECT SYNTAX

prma7799 — Tue, 20 Jun 2017 09:32:31 GMT

Can you please share script error

Re: SQL SELECT SYNTAX

Tue, 20 Jun 2017 09:34:38 GMT

Re: SQL SELECT SYNTAX

Kushal_Chawda — Tue, 20 Jun 2017 09:36:12 GMT

try this

SQL Select

LIFNR,

GJAHR,

MONAT,

BLART,

SUM(DMBTR) AS [AMOUNT]

FROM

BSAK

WHERE GJAHR = '2018' AND BLART in('RE' , 'KR')

GROUP BY LIFNR, GJAHR, MONAT, BLART;

Re: SQL SELECT SYNTAX

Tue, 20 Jun 2017 09:41:41 GMT

Thank ypu for the reply Kushal, unfortunately this does not work. I commented out the where clause and i still get the same error. With the where clause I get this error:

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "IN(" is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)

SQL Select

LIFNR,

GJAHR,

MONAT,

BLART,

SUM(DMBTR) AS [AMOUNT]

FROM

BSAK

WHERE GJAHR = '2018' AND BLART in('RE' , 'KR')

GROUP BY LIFNR, GJAHR, MONAT, BLART

Re: SQL SELECT SYNTAX

prma7799 — Tue, 20 Jun 2017 09:41:54 GMT

Please check format for this

GJAHR = '2018'

BLART = 'RE'

BLART = 'KR'

Re: SQL SELECT SYNTAX

jonathandienst — Tue, 20 Jun 2017 09:50:35 GMT

Is GJAHR really a text field? Remove the quotes around the year value.

Re: SQL SELECT SYNTAX

Anil_Babu_Samineni — Tue, 20 Jun 2017 09:54:15 GMT

For funny solution may be here

in ('RE' , 'KR') // I make it as Space over IN operator

Re: SQL SELECT SYNTAX

Tue, 20 Jun 2017 10:53:43 GMT

It seemed that removing all the commas and putting a space after and before the brackets in the WHERE clause ( BLART = 'RE' OR BLART = 'KR' ) did the trick, thank yo for all the help, I appreciate it.

Re: SQL SELECT SYNTAX

Anil_Babu_Samineni — Tue, 20 Jun 2017 11:06:18 GMT

Is that trick make sense? Or still you are expecting some thing from community?

If you got answer as you expected, Better to close this thread by flag correct answer and helpful answers as you feel

Don't hesitate to ask while you get expected result.