Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
The outer quotes shouldn't be there at all I think. Usually it's WHERE Foo IN ('A','B','C')
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
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)';
???
Omg. there is no need to write the single quotes before and after declaration of variable in sql syntax.
thanks a lot )))
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