SQL Syntax error

[lylererger]

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?

[Peter_Cammaert · Accepted solution]

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.

[Gysbert_Wassenaar]

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

[lylererger]

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

[lylererger]

May be something wrong in that steps:

1.

[$(vPrefix)]: 

LOAD 

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

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

2.

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

3.

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)';

???

[lylererger]

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);

[Peter_Cammaert]

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