Howto use field-value previously charged in another sql statement in script

[#1]

I want to use the value of a field previously charged from a table by sql, in the next sql-statement. such as

Table1:

select CodeId, date from outTable1 where date = x;

table2:

select CodeId, other from outTable2 ...

and now what I want, use the value of previous Table1.CodeId as selection for table2, sucha as

where outTable2.CodeId in (select, load, exist wathever Table1.CodeId));

I don´t know if I make myself understod.

Supposedly Table1 is now resident, so I should be able to read Table1.CodeId to compare in teh script.

Haven´t found solution.

The hint is that outTable2 doesn´t have Date-field, I have to use the key-field CodeId to make an incremental load, of just new records on outTable1 and outTable2

Thanks

JuanJo

[Miguel_Angel_Baeyens]

Hi,

aJuanJoq wrote:strange, should work. Does the in statement have a limit to the size of the list? Does the capacity of a variable has any limitation? Maybe it fails to read the whole variable values.

It depends on your database and odbc driver, but it's likely to be some limitation on the parameters that can be passed in one sentece. Another option would be to set the IN clause in the LOAD part instead of the SQL part. Untested though, it should look like

Table2:LOAD * WHERE MATCH(CodeId, $(vCodes)) > 0;SQL SELECT *FROM Table;

I don't think either this as a good solution in terms of performance.

Regards.

[#3]

First of all I want to beg your perdon. I had a semicolon after the table and before the where statement of the WHERE IN. Sorry.

But anyway it doesn´t work, now it crashes and doesn't load the whole table which is right.

It may be, not working, because cannot read the whole variable vCodes, when I receive the error, shows me lots of IN('value1','value2','value3'... but not the whole variable value, truncated.

I'm using TransTools engine

JuanJo

[#4]

and Miguel, your last offer doesn't work. freezes

[johnw]

Your guess that it isn't passing the whole thing to the database sounds reasonable. In that case, there's no way around it that I'm thinking of. Miguel's version moving the match to the load will just reintroduce the performance (as he suggests), since you're now reading the whole table again, and only throwing out rows after retrieving them to QlikView. Not sure why it isn't working at all, though.

So perhaps the best option for this specific case is the one you already have working now, with the join in SQL. Just keep the other one in mind for future cases where the list you need to pass is shorter. How short is an open question.

[#6]

John Witherspoon wrote:
Your guess that it isn't passing the whole thing to the database sounds reasonable. In that case, there's no way around it that I'm thinking of. Miguel's version moving the match to the load will just reintroduce the performance (as he suggests), since you're now reading the whole table again, and only throwing out rows after retrieving them to QlikView. Not sure why it isn't working at all, though.
So perhaps the best option for this specific case is the one you already have working now, with the join in SQL. Just keep the other one in mind for future cases where the list you need to pass is shorter. How short is an open question. <div></div>

Thanks John, and all others too. 🙂

Just want to mention two things. First, as I said above, I had a semicolon, after from table and before where field in($(vCodes), sorry, that's why it was rezadin the whole table, instead of crashing, after that was the error in reading the sentence where field in (...). Anyway, it doesn´t work, once corrected the missmatch.

second, with Miguel's version, all the chr(39) get in place right. But again it doesn't work.

The reason could be that of not being able to read the whole variable. But, sure I will keep this solution in mind in order to try it in other circumstances.

Thanks

JuanJo

[#7]

Finally, I got it working, as I was fighting with a variable date, that looked like right but didn´t work. I have found the solution, or why it didn´t work, with the sentence IN....

It was the caracter used to delimiter the string to be compared, I must use chr(034)" doble and not chr(039)' simple, because of my database sql.

I have remade the peek('Codes') solution and works just fine, finally..

Thanks

JuanJo

[#8]

I have done the test with less than one day loading, no more tan 20 registers, up to 100 works fine, upper fails to read...