I don't see why if then won't work within FOR loop. but to get the syntax right, I would prefer having the script so that I can test run it. I know some other experts may be able to help you just by looking at the code, but if possible would you be able to share a app with some data and the above mentioned code where it doesn't seem to be working?
Look for below example;
Name AS TableNames;
SELECT Name FROM ContosoRetailDW.sys.tables;
Let vTableCount = NoOfRows(‘SQLTableList’);
For i = 0 To $(vTableCount) – 1
LET vMyTableName = Peek(‘TableNames’, $(i), ‘SQLTableList’);
SQL SELECT * FROM ContosoRetailDW.dbo.$(vMyTableName);
STORE $(vMyTableName) INTO $(Directory)$(vMyTableName).QVD;
DROP Table $(vMyTableName);
thanks, Imran, but I think my issue has to do with the fact that my LOAD statement is nested in two IF statements,
which I don't see in your example above.
As I said, my script completes without error, but "StageDiff" does not get added to either the existing OM table, nor does it get added to the StDiff: table I tried to create (and as I noted, regardless of whether that table label is present or not, the script completes but the data disappears).
I made some changes in my code, and found the following.
First, the new code (contains some lines so I could watch the debugger)
In a limited load using the debugger, I noted that the first two lines of the OM file had the same company and the same OPID, so I would have expected that after the statement
it would try to execute the LOAD statement.However, the debugger just showed it skipping directly to the ENDIF's. Here's a screen grab from the debugger, showing COMP1=COMP2 AND OP1=OP2.
But the "THEN" statement (i.e. the LOAD block) is not executing. I can't figure out why, unless this is not supported, and the non-support is not documented.
Thanks, Christian. I wasn't aware of the "Previous" function, and wouldn't have thought of it in any case, as the table is not sorted by company coming in. However, after looking at what you suggested, it dawned on me that the LOAD statement gets executed AFTER the OM table is built, and hence it would already be ordered by Company, OPID, OnDate.
thanks a bunch to everyone!