Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Internal SQL Server Error - Subquery and join

Posted on 2002-05-14
10
Medium Priority
?
444 Views
Last Modified: 2012-06-27
Hi - I know this is a bit of big question but I am really mystified.

I have a query which should be based on a view (I have put the view's query inside for this example) which needs to have the result put into a variable. Running this example causes error 8624 - 'Internal SQL Server Error'. It does not cause an error when: you remove the inner join to the view; OR you just run the query without passing it into a variable; OR you remove the WHERE clause with the subqueries.

Here is the example:

declare @var int

select @var = (

select count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
     r2.[sequence] =
     CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                     WHERE      r3.request_id = r1.request_id AND
                         r3.event_type = 'RELEAS' AND
                          r3.[sequence] > r1.[sequence]
                     )
          THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                 WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
                )
          ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                 WHERE  r5.request_id = r1.request_id AND
                     r5.event_type = 'RELEAS' AND
                     r5.[sequence] > r1.[sequence]
                )
          END
--***** end of view
) re
inner join request r on re.request_id = r.request_id
)

select @var
0
Comment
Question by:griffinj01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 2

Expert Comment

by:BobSnyder
ID: 7008526
Two suggestions
Use sp_configure to check the query governor cost limit, it should be 0 to remove limits.
If any join column is NULLABLE, try converting its values to non null like
ISNULL(r3.event_type, '') = 'RELEAS'

The query optimizer goes through a lot of extra work if a column allows nulls.
0
 

Author Comment

by:griffinj01
ID: 7008845
Thanks for the ideas but query govenor cost limit is 0 and all columns are not nullable.
0
 
LVL 1

Expert Comment

by:falconew
ID: 7010090
Hi,

When you run it without passing it into variable what is the output?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:griffinj01
ID: 7010448
The output is just a normal count.

eg.            
-----------
379

(1 row(s) affected)

 
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 1600 total points
ID: 7011251
Try this instead . . . .

declare @var int

select @var = count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
    r2.[sequence] =
    CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                    WHERE      r3.request_id = r1.request_id AND
                        r3.event_type = 'RELEAS' AND
                         r3.[sequence] > r1.[sequence]
                    )
         THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                WHERE  r4.request_id = r1.request_id AND
                    r4.event_type = 'COMPLT' AND
                    r4.[sequence] > r1.[sequence]
               )
         ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                WHERE  r5.request_id = r1.request_id AND
                    r5.event_type = 'RELEAS' AND
                    r5.[sequence] > r1.[sequence]
               )
         END
--***** end of view
) re
inner join request r on re.request_id = r.request_id


select @var
0
 

Author Comment

by:griffinj01
ID: 7011272
Excellent! Thanks spcmnspff. Do you want the points?

Why would that work and not the way I tried?
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7011307
Yeah I would like the points =).  Uhhh - My guess is that there were too many selects and the optimizer got confused, but really, who knows . . . .  One other thing, I was working on rewriting your view.  It would probably be faster with a union and a not exists rather than a cross join and a case.  Hold on I'll send you that too . . . .
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7011330
griffinj01:

I was thinking of something like this for th view:

SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
Where r2.[sequence] =  ( SELECT MIN(r5.[sequence]) FROM request_event r5
                   WHERE  r5.request_id = r1.request_id AND
                   r5.event_type = 'RELEAS' AND
                   r5.[sequence] > r1.[sequence]

    AND EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                  WHERE      r3.request_id = r1.request_id AND
                  r3.event_type = 'RELEAS' AND
                  r3.[sequence] > r1.[sequence])
   
UNION ALL
                   
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
   r2.[sequence] = ( SELECT MAX(r4.[sequence]) FROM request_event r4
                     WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
              )  
   
   AND NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                   WHERE      r3.request_id = r1.request_id AND
                       r3.event_type = 'RELEAS' AND
                        r3.[sequence] > r1.[sequence]

It actually turns out to bo not much different than yours, but see if the reads improve with this one  . .  .
0
 

Author Comment

by:griffinj01
ID: 7011348
Cool - I'll give that a go.  Thanks very much.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 7011416
Here is another possibility for the view.  I am not able to test it, but you might want to try it just in case:

SELECT r1.request_id, r1.event_type, r1.event_dt
FROM request_event r1
INNER JOIN
(
SELECT r2.request_id, MAX(r2.[sequence]) AS maxSeq, MIN(r2.[sequence]) AS minSeq
FROM request_event r2
WHERE r2.request_id = r1.request_id AND
((r2.event_type = 'RELEAS' AND maxSeq > r1.[sequence]) OR
(r2.event_type = 'COMPLT' AND minSeq > r1.[sequence]))
GROUP BY r2.request_id
) AS r2e ON r1.request_id = r2e.request_id

You will need index(es) on request_id (very likely already present) and [sequence] (probably present as well).  For the purposes of this query, it would help if you added event_type as an additional column in (one of) the index(es); that is, if it's a large table.  Then the query will be able to complete using only the index, without accessing the primary table at all.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question