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