SQL dynamic scripting - where A <> 'x'

Tue, 28 Jul 2015 08:28:44 GMT

Good day,

I'm creating a multi source script that extracts the same query from multiple sources.

Due to the nature of the database structure, it requires dynamic queries.

This works ok, unless i want to add non-numerical values to filters.

In SQL these have to be surrounded by '

But as this is a control symol in QV, this breaks the script.

Does anybodt know a resolution for this problem?

Tnx, karel

Example:

$(vOutput):
LOAD SRC_Database, SRC_Company INLINE
[SRC_Database, SRC_Company
];

Let vDatabaseMainCount = NoOfRows('DatabaseMain');
For k = 0 To $(vDatabaseMainCount)-1

Let db = Peek('Database',$(k),'DatabaseMain');
Let sql = Peek('SQL',$(k),'DatabaseMain');
Let arc = Peek('SRC_Database',$(k),'DatabaseMain');
;
OLEDB CONNECT TO
[Provider=SQLOLEDB.1;Integrated Security=SSPI;Connect Timeout=2;'Persist Security Info=False;Initial catalog=
$(db)
;Data Source=
$(sql)
;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=S-BE-KI-XTS16;
Use Encryption for Data=False;Tag with column collation when possible=False]
;
Company:
SQL SELECT Name FROM $(db).dbo.Company;
if ScriptError = 0 then
Let vCompanyCount = NoOfRows('Company');
   For i = 0 To $(vCompanyCount)-1
    LET vMyCompany = Peek('Name',$(i),'Company');
    LET vCompany = '$(vMyCompany)$';

// The Query
// example on company tables: select * from $(db).dbo.[$(Company)power customer]
// example on database tables: select * from $(db).dbo.[power logging]

    LET vQuery =
    '
    select * from $(db).dbo.[$(vCompany)power customer]

     where DT_Changed > '1/1/2015'
    '
    ;
    One:
    LOAD '$(arc)' as SRC_Database,'$(vMyCompany)' as SRC_Company,*;
    SQL $(vQuery);
    ;
    Concatenate ($(vOutput)) LOAD *, '1' as ID resident One;
    drop table One;
Next i;
drop table Company;
else
endif
next k;

Re: SQL dynamic scripting - where A <> 'x'

Fri, 31 Jul 2015 07:52:37 GMT

Found workaround using integer, these don't require ''

where datepart(yyyy,dt_changed)*10000+ datepart(mm,dt_changed)*100+datepart(dd,dt_changed > 20150101

topic SQL dynamic scripting - where A <> 'x' in QlikView

SQL dynamic scripting - where A <> 'x'

Re: SQL dynamic scripting - where A <> 'x'