Re: SQL-Query with many equal subselects

losthighway_at_metalogic.ch (Steve) wrote in message news:<9606de71.0304010354.28bbd9aa_at_posting.google.com>...
> I've searched the web for hours to find a solution for the following
> question, but nobody seems to have this problem except me ;-)
>
> I created a simple query that shows the problem. As you can see there
> are 2 main queries, both of them use an additional filter (SELECT id
> FROM T_profession WHERE id < 1000). It's quite unefficient to run this
> sub-query twice, so is it possible to place this query somewhere else
> and just run it once ? Making a view doesn't work as this subquery is
> being build at runtime within an application.
>
> Any help would be highly appreciated...
> Steve
>
>
> SELECT *
> FROM (SELECT 'Engineer', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Engineer%'
> AND id IN (SELECT id FROM T_Profession WHERE id < 1000)),
> (SELECT 'Manager', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Manager%'
> AND id IN (SELECT id FROM T_profession WHERE id < 1000))
>
> --------------------------------------------------------

You can try this way
select count(case when instr(description,'Engineer') > 1 then 1 end),

      count(case when instr(description,'Manager') > 1 then 1 end)    from t_profession where Id between 16000 and 35000 This gave an elapsed time of 00:00:00.13 Your query gave me an elapsed time of 00:00:00.68. Good luck. Received on Tue Apr 01 2003 - 16:42:26 CEST