SQL statement crashing the SQL broker

[JeremyGiberson]

I am posting to find out if any one has heard of an sql statement crashing the SQL broker.

Today, I was trying to perform a select statement with a nested query, when I executed the the SQL statement odbc errored with a "Server not reachable or possible RPC error". We were unable to get the broker to accept any more queries after that until we restart the broker.

I did a few tests to make sure it was the query that was causing this error and not just some random coincidence that the error occured when I attempted the query. WE have four brokers, two for SQL , two for 4GL. One of each broker for live, and for test database. I did my following tests on the SQL dedicated test db broker.

First I did a normal select

SELECT * FROM pub.part WHERE "part-num" = '1221-0011R'

Result: Part record for 1221-0011R

Then, I attempted my nested query

SELECT * FROM 
(SELECT MAX("ar-seq"), MIN("ar-amtdue"), "inv-num" FROM pub.acctrec WHERE "cust-num" = '64047' GROUP BY "inv-num") as temprec
WHERE "ar-amtdue" > 0

Result: ODBC ERROR: Server not reachable or possible RPC error

Then, I attempted the normal select once again, only to get the same error. I closed my connection to the broker, opened a new one and was once again able to do a normal select but when I tried the nested select I got the error again.

Even though the broker (for the test db) was responsive once I closed the connection and opened it back up, this morning when we experienced the problem on the live broker, we were unable to get the broker to respond even on new connections and had to do a quick restart of the database (wich auto shuts down and auto starts the brokers).


Do any of you have any ideas why this might have occured? As a work around, we are just performing the inner select, and doing the amtdue comparison in program code with a while loop. But this method really erks me as it *could* be done in one query instead of being broken up into a database call and programming code.


Oh, we use:
ODBC DRIVERS: DATADIRECT 4.10 32-bit Progress SQL92 v9.1E
Progress version 9.1E

 

[BCM]

I have a hunch about the problem. Your nested select statement returns a resultset and that set is given the alias 'temprec' by your code: ') as temprec'. Though the use of the word 'as' is widely accepted for aliasing a column, it is not widely accepted when aliasing sets. So, remove the word 'as'. Make it ') temprec'.

 

[BCM]

Also, you do not need to use a nested query. You should use the HAVING clause. HAVING works like WHERE only HAVING applies itself to the results of aggregate functions. Think of it this way: WHERE limits the records going in to the set, and HAVING limits the records coming out of the set.

SELECT MAX("ar-seq"), MIN("ar-amtdue"), "inv-num"
FROM pub.acctrec
WHERE "cust-num" = '64047'
GROUP BY "inv-num"
HAVING MIN("ar-amtdue") > 0

 

[JeremyGiberson]

Hi BCM,
Thanks for the reply. Currently we are busily updating a bunch of stuff after a network change last night so I don't have time to play around with the query right now, but I did want to say a few things. When I was reading the SQL-92 reference the reference for the nest query actually specifiies that as is used to assign the name to the result set. Either way I plan to give it a try later.

As far as using HAVING goes, I've already tried that. Infact, using the "HAVING" clause was our first attempt to retreive the results desired. However the "HAVING" clause actually precedes the selection statement. Consider this lame example.

table arseq
ar-seq | ar-amtdue | inv-num 
0 | 10 | #2501
1 | 5  | #2501
2 | 0  | #2501
0 | 17 | #2502
1 | 0   | #2502
0 | 25 | #2503
1 | 15 | #2503

If I tried

SELECT MAX("ar-seq"), MIN("ar-amtdue"), "inv-num" 
FROM pub.acctrec 
WHERE "cust-num" = '64047' 
GROUP BY "inv-num"
HAVING MIN("ar-amtdue") > 0

My goal is to pull the invoice that has not been paid off yet. Which is :

1 | 15 | #2503

My results are

1 | 5  | #2501
0 | 17 | #2502
1 | 15 | #2503

The reason is that the select statement will qualify the HAVING clause during the select instead of after.