I'm trying to perform several actions in one SQL,
so i can provide my users with a simple sql query they can run externally (odbc), instead of me writing dedicated code to perform the query.

The original process in the code is, running this query:

SELECT ID
FROM LOOKUP_SOFTWARE_PRODUCT
WHERE UPPER(VALUE) like UPPER('%bla bla%')
------------------------------------------------

This results in a dataset contining 3 items:  (3438, 3476, 3855)  )

now i do this:
------------------------------------------------
SELECT
 DISTINCT COMPUTER_ID, COMPUTER_NAME, USER_NAME, IP_ADDRESS

FROM
 COMPUTERS

JOIN COMPUTER_HARDWARE ON (COMPUTER_ID=ID)
JOIN LINK_COMPUTER_SOFTWARE_REGISTRY LINK_TABLE ON (COMPUTER_HARDWARE.COMPUTER_ID=LINK_TABLE.COMPUTER_ID)

WHERE
 LINK_TABLE.SOFTWARE_PRODUCT_ID in (3438, 3476, 3855)
/* these are values i retrieve from previous SQL, by code */

ORDER BY
  COMPUTER_NAME
------------------------------------------------

can this be achieved in just one sql?
Much obliged
Amir

"Amir Netiv" <amir@prefixit.com> wrote in message
news:8CAFC1C9-F5CD-492A-8B85-20FC6909366A@news.elevatesoft.com...
>
> WHERE
>  LINK_TABLE.SOFTWARE_PRODUCT_ID in (3438, 3476, 3855)
> /* these are values i retrieve from previous SQL, by code */
>

You can simply replace the literals inside the in parentheses with your
original query. WHERE FIELD IN (SELECT ...)

Be careful you might get a big performance hit, depending on the size of
LOOKUP_SOFTWARE_PRODUCT.

Another option would be to add LOOKUP_SOFTWARE_PRODUCT to the list of joined
tables

SELECT
 DISTINCT COMPUTER_ID, COMPUTER_NAME, USER_NAME, IP_ADDRESS

FROM
 COMPUTERS

JOIN COMPUTER_HARDWARE ON (COMPUTER_ID=ID)
JOIN LINK_COMPUTER_SOFTWARE_REGISTRY LINK_TABLE ON
(COMPUTER_HARDWARE.COMPUTER_ID=LINK_TABLE.COMPUTER_ID)
JOIN LOOKUP_SOFTWARE_PRODUCT ON (LINK_TABLE.SOFTWARE_PRODUCT_ID =
LOOKUP_SOFTWARE_PRODUCT.ID)

WHERE
 UPPER(VALUE) like UPPER('%bla bla%')


Maybe performance is better with the JOIN, try both.

Robert

Amir,

<< I'm trying to perform several actions in one SQL, so i can provide my
users with a simple sql query they can run externally (odbc), instead of me
writing dedicated code to perform the query. >>

Just a quick note - the DBISAM ODBC driver can execute SQL scripts just
fine, although the client application being used may or may not support the
submittal of raw SQL scripts.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

<<
Just a quick note - the DBISAM ODBC driver can execute SQL scripts just
fine, although the client application being used may or may not support the
submittal of raw SQL scripts.
>>

Got it.
I provide an SQL query screen in my application, directly over the application's database.
This allows users to run their own scripts and not just depend on our hardcoded reports.

Thanks for the suggestion, it does the job,
i'll see which option is more efficient.