Is it possible to manipulate table variables in calc view

[patrickbachmann]

Hi experts,

I am wondering at a very high level if something like the following scenario is possible in a calculation view?

For example;

VAR1 = select * from ABC

VAR2 = select * from XYZ

Then step through each single entry of the resultset records for VAR1 and for each record perform a lookup in second resultset records for VAR2.  If a match is found then manipulate VAR2 resultset by appending a FLAG column at the end with a simple Y or N. 

Finally my VAR_OUT will be results of the manipulated VAR2 table.  I don't need actual code I'm just at this point wondering if this is possible or has anybody has done something similar before.  Or at very high level how you might approach it. 

Thanks,

-Patrick

[former_member182114 · Accepted solution]

Hi Patrick,

You can do somethink like this by script with cursors, loop, array BUT your description points just to a simple LEFT OUTER JOIN of table XYZ against ABC.

Link the two tables and project all XYZ data and a column of ABC table. Your new column FLAG can be created with a test of ISNULL of ABC table.

Best regards, Fernando Da Rós

[patrickbachmann · Accepted solution]

Hi Fernando,

Thanks for your cursor suggestion, that's what I was starting to lean towards.  As for the join of the two tables unfortunately it's not that simple as there is no common keys to join in each table, or I should say only a partial key. Then sum up X amount of values from the VAR2 table and then mark them as processed so the next iteration doesn't use them again.  It's all over simplified in my example, just curious if it could be done.  I don't suppose you have any good code snippet of cursor with loop?  I'm also searching through latest SQL guide for examples.

Thanks,

-Patrick

[former_member182114 · Accepted solution]

Hi Patrick,

Well, you can give a try on it. You can borrow samples of cursor and while here (from Ravindra and Wolf):

Also you may need more tools, take a look on temporary tables (to persist and flag) OR give a look on arrays.

Other option that I can think is to just do loops over first cursor and selections using NOT exist or IN...

Anyhow, to see the optimal option only can be made with all cards on deck. If you can provide a not so deep but understandable sample we can help a bit.

Best regards, Fernando Da Rós

[patrickbachmann · Accepted solution]

Hi Fernando,

Actually this is good to get me started.  I'm going to start experimenting with your samples today.  Will close this thread soon, just see if anybody else has anything to add.

Thanks again,

-Patrick

[patrickbachmann · Accepted solution]

Thanks Fernando.  You have led me down the path I was searching for.

-Patrick