topic Re: SQL Syntax error in QlikView

SQL Syntax error

lylererger — Wed, 19 Oct 2016 17:04:06 GMT

Hi Community!

Why...why QlikView by transmission of a variable into the SQL-query replaces single quotes with double?

As a result writes that a syntax error. how to eliminate this error?

Re: SQL Syntax error

Peter_Cammaert — Wed, 19 Oct 2016 17:15:26 GMT

Usually it doesn't do that.

Something else may be wrong in your code. Can you post your script, or at least a big chunk of it that contains the erroneous code? Thanks.

Re: SQL Syntax error

Gysbert_Wassenaar — Wed, 19 Oct 2016 17:35:30 GMT

The outer quotes shouldn't be there at all I think. Usually it's WHERE Foo IN ('A','B','C')

Re: SQL Syntax error

lylererger — Wed, 19 Oct 2016 18:41:14 GMT

I too think so, but still can't identify problem %(

Here is the code:

LET vTaskCode = 'E315';


IF vScript LIKE '*+$(vTaskCode)*' THEN


  TRACE [-----$(vTaskCode)-----];


  //-----------Каталоги проекта


  CALL ProjectFolders(1)


  //-----------Строка подключения


  CALL Connection(1);

  //-----------Периодичность загрузки

  CALL Reload(vReload);


  //-----------Источники и приемник данных

  LET vPrefix = 'DOGOVOR';
  LET vTable = 'PAX';

  LET vDateStart = Num(vDateStart);
  LET vDateFinish = Num(vDateFinish);

  //----------Цикл от большего к меньшему


  LET vStartYear = Year(vDateStart);
  LET vStartMonth = Month(vDateStart);

  LET vLastYear = Year(vDateFinish);
  LET vLastMonth = Month(vDateFinish);

  LET vLoadYear = vLastYear;
  LET vLoadMonth = vLastMonth;

  DO WHILE vLoadYear >= vStartYear
  DO WHILE (vLoadMonth >= 1 AND vLoadYear > vStartYear) OR (vLoadMonth >= vStartMonth AND vLoadYear = vStartYear)

  LET vLoadMonthStr = NUM(vLoadMonth, '00');
  LET vPeriod = $(vLoadYear)$(vLoadMonthStr);

  TRACE;
  TRACE [-----ОБРАБОТКА ПЕРИОДА $(vPeriod)-----];
  TRACE;

  IF QvdNoOfRecords('$(vDataTier1)\$(vPrefix)_$(vPeriod).qvd')>0 then

  [$(vPrefix)]:
  LOAD
 Concat(Chr(39)&DG_CODE&Chr(39),',') as list
  From [$(vDataTier1)\$(vPrefix)_$(vPeriod).qvd] (qvd);

  LET vData = '(' &FieldValue('list',1) & ')';

  LET vRows = NoOfRows('$(vPrefix)');


  tmpData: LOAD * Inline [tmpField];

  FOR vIx=0 to vRows-1

  LET vI = vIx + 1;

  TRACE [----ОБРАБОТКА СТРОКИ $(vI) ИЗ $(vRows)-----];

  LET vDG_CODE = Peek('DG_CODE',vIx,'$(vPrefix)');

  tmp:
  NoConcatenate
  SQL SELECT DG_CODE, dg_nmen-(cast(oneway as decimal)/2) as pax
  FROM DOGOVOR
        OUTER APPLY(select count(*) oneway from (
              SELECT tu_key, count(*) as cnt from turist join turistservice on tu_key=tu_tukey join dogovorlist on dl_key = tu_dlkey and dl_svkey=1 where tu_dgcod=dg_code group by tu_key
              ) tu where cnt=1 and not exists(select 1 from Dogovorlist dlt where dlt.dl_dgcod=dg_code and dl_svkey in (3,2,4))
              ) ow
  WHERE DG_CODE in '$(vData)';

  EXIT Script

  IF NoOfRows('tmp')>0 THEN

  Concatenate (tmpData)
  LOAD
  *
  Resident tmp;

  ENDIF

  CALL DropTables('tmp');


  NEXT vIx


  DROP Field tmpField from tmpData;

  IF NoOfRows('tmpData')>0 THEN

  LET vtmpFile = '$(vDataTier2)\$(vTable)_$(vPeriod).qvd';

  EXECUTE cmd.exe /C del /Q $(vtmpFile);

  STORE tmpData INTO  [$(vtmpFile)] (qvd);

  ENDIF

  CALL DropTables('tmpData|$(vPrefix)');

  ELSE

  TRACE [-----НЕТ ДАННЫХ ЗА ПЕРИОД $(vPeriod)-----];

  ENDIF

  LET vLoadMonth = vLoadMonth - 1;
  LOOP

  LET vLoadMonth = 12;
  LET vLoadYear = vLoadYear-1;
  LOOP

  DISCONNECT;


ENDIF

IF vScript LIKE '*$(vTaskCode)-*' THEN
  EXIT Script;
ENDIF

Re: SQL Syntax error

lylererger — Wed, 19 Oct 2016 18:45:32 GMT

May be something wrong in that steps:

[$(vPrefix)]:

LOAD

Concat(Chr(39)&DG_CODE&Chr(39),',') as list

From [$(vDataTier1)\$(vPrefix)_$(vPeriod).qvd] (qvd);

LET vData = '(' &FieldValue('list',1) & ')';

tmp:

NoConcatenate

SQL SELECT DG_CODE, dg_nmen-(cast(oneway as decimal)/2) as pax

FROM DOGOVOR

OUTER APPLY(select count(*) oneway from (

SELECT tu_key, count(*) as cnt from turist join turistservice on tu_key=tu_tukey join dogovorlist on dl_key = tu_dlkey and dl_svkey=1 where tu_dgcod=dg_code group by tu_key ) tu where cnt=1 and not exists(select 1 from Dogovorlist dlt where dlt.dl_dgcod=dg_code and dl_svkey in (3,2,4))) ow

WHERE DG_CODE in '$(vData)';

???

Re: SQL Syntax error

lylererger — Wed, 19 Oct 2016 18:48:45 GMT

Omg. there is no need to write the single quotes before and after declaration of variable in sql syntax.

Re: SQL Syntax error

gwassenaar

thanks a lot )))

WHERE DG_CODE in $(vData);

Re: SQL Syntax error

Peter_Cammaert — Wed, 19 Oct 2016 20:46:18 GMT

Good catch. If I change my own example into this,

I get the same single-quote-into-double-quote substitution. Should remember that.

Thanks for the points, but consider changing the Correct assignment to Gysberts post as he pointed out the real cause of your problem. You can still change it. Moreover, it looks better when the original question and the correct answer are combined in the same frame at the top of this thread.

If you think other posts provded useful feedback, you can still assign Helpfuls by selecting the corresponding item from the Actions menu.

Good luck,

Peter