union

[sourceuno]

Is there any way to do a union of 2 queries like you can with SQL? I'm trying to do a search across 2 tables that have identical fields but must exist as separate tables.

[Kai]

Do each query sequentially in Vortex:

<SQL ROW "select result, .... from table1 where ...">
print result
</SQL>

<SQL ROW "select result, .... from table2 where ...">
print result
</SQL>

If you need to save the returned variables instead, just combine them with a variable assignment:

<$TotResult = >
<SQL "select result, ... from table1 where ..."></SQL>
<$TotResult = $TotResult $result>
<SQL "select result, ... from table2 where ..."></SQL>
<$TotResult = $TotResult $result>

You'll need other $Tot... variables for the other columns too.

[sourceuno]

That might work for me, but I also need to be able to sort the union of those 2 queries by the rank from a likep query. Is this possible?

[bart]

<sql novars "create ram table X as select result, .... from table1 where ...">
</sql>

<sql novars "insert into X select result, .... from table2 where ...">
</sql>

<SQL ROW "select * from X order by RankValue ">
print result
</SQL>

[sourceuno]

Is this method of joins limited my memory? I have a table with 35000 rows that I'm trying to join with another table with just a few rows. I get the following error at the 2nd sql statement in the above post:

002 Jun 8 12:37:08 /vortex/multisearch:350: Can't create temporary KDBF file c:\tmp\T00436a: error 2: No such file or directory in the function kdbf_open
000 Jun 8 12:37:08 /vortex/multisearch:350: Monitor ABEND exception 0xC0000005 (ACCESS_VIOLATION)

[John]

You can issue the following before you start:

<SQL "set ramrows=0"></SQL>

By default after a large amount of data is stored in memory then Texis will use a file to free up memory. It is trying to create the file in c:\tmp.

[sourceuno]

Now I get this timeout error when doing a search across 3 tables:

Timeout
Your request exceeded the time limit. Please try again.

Because my license limits me to 100,000 rows per table, I need to split my table of more than 300,000 rows into 3 tables. As my table grows, I will need to split it into even more. Is this feasible, splitting a table into multiple tables and searching across those tables? Can you recommend any other way of doing this without upgrading my license to more records per table?

[John]

The real solution here is to get the appropriate size license, and that way you will be able to write the queries so they can be performed efficiently, without making the engine do a lot of work to get around the license.