SET and LET and parameters

Anonymous — Wed, 07 Nov 2012 02:04:57 GMT

Hi,

I can make to easy testing reloads in develop application. So I have to restrict count of records.

Now we set in all SELECT "TOP..." or "WHERE Date <, >, BETWEEN..." and comment and uncomment it... we are doing mistake and it's time-expansive.

I can to have one variable and on this I can use restriction (testing mode) or no (really reload).

SET QV_ModeReally = 1;  // Full reload
SET QV_ModeTest_TOP = 2;  // TEST with TOP restrictions
SET QV_ModeTest_WHERE   = 3;  // TEST with WHERE restrictions
SET QV_Mode=<x>;

So, if I can use TOP, it's OK - I can use one formule for all tables:

SET QV_ClausTop =  " TOP 1000 ";
SET QV_Mode=2;
LET QV_TOP = IF($(QV_Mode)=$(QV_ModeTest_TOP), '$(QV_ClausTop)');
.
.
.
Table1:
LOAD Cis,
    Descr,
    Txt,
    Typ,
    Zkr1;
SQL SELECT $(QV_TOP)
    Cis,
    Descr,
    Txt,
    Typ,
    Zkr1
FROM $(AKTDB).dbo.SPESTR  WHERE Typ = $(SPS_Typ_Kod_Zakazky);

But if I can use condition into WHERE (it's often mode), I have to have many many conditions - for each strictly name of field in SELECT one special condition:

SET QV_ClWhere_DatP =  " (DatP BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";
SET QV_ClWhere_Date =  " (Date BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";
SET QV_ClWhere_DVyst =  " (DVyst BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";

SET QV_Mode=3;
LET QV_WHERE_DATP = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_DatP)', ' 1=1 ');
LET QV_WHERE_DATE = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_Date)', ' 1=1 ');
LET QV_WHERE_DVYST = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_DVyst)', ' 1=1 ');
.
.
TAB1:
LOAD 
.
.
SQL SELECT $(QV_TOP)
.
.
FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)
WHERE 
$(QV_WHERE_DatP) AND
. <other conditions>
.
;
TAB2:
LOAD 
.
.
SQL SELECT $(QV_TOP)
.
.
FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)
WHERE 
$(QV_WHERE_DVyst) AND
. <other conditions>
.
;

I ask for possibilities use some construction LET / SET / $1 / " / ' ... - call main condition for datetime type with parameter:

?SET? QV_ClWhere_Date =  " ($1 BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";
?LET? QV_WHERE_DATE = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_Date($1))', ' 1=1 ');
TAB1:
LOAD 
.
.
SQL SELECT $(QV_TOP)
.
.
FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)
WHERE 
$(QV_WHERE_DATE(?DatP?)) AND
. <other conditions>
.
;

Please, is some way for this?

Many thanks,

Olda

topic SET and LET and parameters in QlikView

SET and LET and parameters