Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Currently I have the following part of my script :
SUB SplitsenNaarMiddel(middel, eersteVraag, laatsteVraag)
FOR vraagNummers = eersteVraag TO laatsteVraag
IF(vraagNummers > 99) THEN
SET output = $(vraagNummers);
ELSEIF (vraagNummer > 9) THEN
SET output = 0$(vraagNummers);
ELSE
SET output = 00$(vraagNummers);
END IF;
TRACE $(vraagNummers);
TRACE $(output);
NEXT
Temp_Middel_$(middel):
LOAD
Veldzoeknaam AS ZoekNaam,
VeldP_id AS $(middel)_VeldP_id,
Middel AS $(middel)_Middel,
MATEnr AS $(middel)_MATE,
IF(Vraagnr=1, VeldWaarde) AS $(middel)_Vraag001,
IF(Vraagnr=2, VeldWaarde) AS $(middel)_Vraag002,
IF(Vraagnr=3, VeldWaarde) AS $(middel)_Vraag003,
IF(Vraagnr=4, VeldWaarde) AS $(middel)_Vraag004,
IF(Vraagnr=5, VeldWaarde) AS $(middel)_Vraag005,
IF(Vraagnr=6, VeldWaarde) AS $(middel)_Vraag006,
IF(Vraagnr=1, VeldTypeGrp) AS $(middel)_Vraag001Type,
IF(Vraagnr=2, VeldTypeGrp) AS $(middel)_Vraag002Type,
IF(Vraagnr=3, VeldTypeGrp) AS $(middel)_Vraag003Type,
IF(Vraagnr=4, VeldTypeGrp) AS $(middel)_Vraag004Type,
IF(Vraagnr=5, VeldTypeGrp) AS $(middel)_Vraag005Type,
IF(Vraagnr=6, VeldTypeGrp) AS $(middel)_Vraag006Type
RESIDENT Data2
WHERE Vraagsort = '$(middel)';
END SUB
My function call is :
CALL SplitsenNaarMiddel('A', 1, 6)
The working of my script is correct but I want to replace my repeating code by using the parameters 1 and 6. 'vraagNummers' are the values 1, 2, 3, 4, 5 and 6 and 'output' 001, 002, 003, 004, 005 and 006 with leading zeroes. I'd like to repeat the rule 'IF(Vraagnr=1, VeldWaarde) AS $(middel)_Vraag001' 6 times with the variables described in the loop and 'IF(Vraagnr=1, VeldTypeGrp) AS $(middel)_Vraag001Type' with the same variables. My code needs to work with each possible number of iteration and start numbers other than 1, like 10 to 20 for example.
Do you have some sample data and your expected result to test a solution with?
Within the load itself you couldn't check/react if there is / should be any fields. This meant you need to build the load-statement on the outside from the load with something like:
FOR vraagNummers = eersteVraag TO laatsteVraag
The same for second field and you need further a counter and/or replace-logic for the last comma and similar things to get in the end a valid load-statement within a single variable (using several variables which are concatenated in the end will be easier than doing it within a single variable). Further I suggest to do it first without an executing of the load else just checking the created load-statement within the trace, log-file and/or a textbox.
Beside this with your approach you are creating multiple different crosstables - quite often is the handling of crosstables much more expensive than using "normale" data-structures. Therefore are you really sure that you need them?
- Marcus