Jump to content

Is SQL a CRIPPLED LANGUAGE?


davej
 Share

Recommended Posts

I thought SQL was supposed to be a very flexible language but everything I try that is beyond trivial fails due to some hidden limitation. Is this due to SQL or am I using a wimpy SQL engine?I thought I had a query that might be useful for the timecard problem; SELECT i.EmpID, i.CardDate, (SELECT MIN(datediff(MINUTE,i.CardDate,o.CardDate)) FROM TIMECARD AS o WHERE i.EmpID = o.EmpID AND o.PunchType = 12 AND o.CardDate > i.CardDate) AS MinsFROM TIMECARD AS iWHERE i.PunchType = 10;But no way! This query falls on its face;Msg 8124, Level 16, State 1, Line 1Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.So I dummy it down to something that isn't even useful;SELECT i.CardDate, (SELECT MIN(datediff(MINUTE,i.CardDate,o.CardDate)) FROM TIMECARD AS o WHERE o.CardDate > i.CardDate) AS MinsFROM TIMECARD AS i;And it still fails!Msg 8124, Level 16, State 1, Line 1Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.So I make it beyond utterly worthless and only one column;SELECT (SELECT MIN(datediff(MINUTE,i.CardDate,o.CardDate)) FROM TIMECARD AS o WHERE o.CardDate > i.CardDate) AS MinsFROM TIMECARD AS i;And it still whines about the multiple columns;Msg 8124, Level 16, State 1, Line 1Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.So what is it whining about?I saw this syntax example online;SELECT e.LastName, e.DepartmentID, (SELECT d.DepartmentName FROM department AS d WHERE d.DepartmentID = e.DepartmentID ) AS DepartmentNameFROM EMPLOYEE AS e;

Edited by davej
Link to comment
Share on other sites

Let me go to a different example. Here is a really ordinary and trivial situation;You have a CUSTOMER table containing customer information including the customer's BALANCE and the REP_ID for the sales rep assigned to that customer.You can query;SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMERGROUP BY REP_ID;and get a list showing the total customer balances per sales rep;REP_ID SUM_OF_BALANCES-------- --------------------------20 27533.0035 8815.7565 11303.00But now what if you only want to see the row(s) with a SUM > 5000 ?Sounds easy!But...SELECT C.REP_ID, SUM(C.BALANCE) AS SUM_OF_BALANCESFROM CUSTOMER AS CWHERE C.REP_ID IN (SELECT R.REP_ID FROM CUSTOMER AS R WHERE SUM(R.BALANCE) > 5000.00)GROUP BY C.REP_ID;Fails...Msg 147, Level 15, State 1, Line 5An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.Swapping HAVING for either or both WHERE does not solve the problem.

Link to comment
Share on other sites

What about:

SELECT * FROM (SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMER GROUP BY REP_ID) WHERE SUM_OF_BALANCES > 5000

SQL is a very flexible database language - just because you may not be able to get things working yet does not mean the entire system is "crippled". Like everything, it takes time to understand how SQL works, and as you get better you will be able to write more complex queries effectively.

Link to comment
Share on other sites

SQL is a very flexible database language - just because you may not be able to get things working yet does not mean the entire system is "crippled". Like everything, it takes time to understand how SQL works, and as you get better you will be able to write more complex queries effectively.
I'm sure every SQL guru will just say I'm being silly to criticize, but... this language has issues.SELECT * FROM (SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCES FROM CUSTOMER GROUP BY REP_ID) WHERE SUM_OF_BALANCES > 5000 ;Does not work and no similar form works with either WHERE, HAVING, SUM_OF_BALANCES, or SUM(BALANCE). These all produce errors similar to;Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'WHERE'.SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMERHAVING SUM(BALANCE) > 5000.00GROUP BY REP_ID;Does not work and no similar form works with either WHERE, HAVING, SUM_OF_BALANCES, or SUM(BALANCE). These all produce errors similar to;Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'GROUP'.EDIT--I did find one solution. You can create a view of the original query and then query the view. Edited by davej
Link to comment
Share on other sites

i think issues are occuring not in the sql enguine but in the sql syntax.

SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMERHAVING SUM(BALANCE) > 5000.00GROUP BY REP_ID;
should be...
SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMERGROUP BY REP_IDHAVING SUM(BALANCE) > 5000.00;

you cant use where clause in agregate function thats for having clause is there.see details here.

Edited by birbal
Link to comment
Share on other sites

i think issues are occuring not in the sql engine but in the sql syntax.should be...
SELECT REP_ID, SUM(BALANCE) AS SUM_OF_BALANCESFROM CUSTOMERGROUP BY REP_IDHAVING SUM(BALANCE) > 5000.00;

you cant use where clause in agregate function thats for having clause is there.see details here.

Oh heck, you're right. I just had the order wrong. This query works in SQL Server 2008.But what about this next case?SELECT P.PART_NUM, P.DESC, P.ON_HAND, SUM(L.NUM_ORDERED)FROM PART PLEFT OUTER JOIN ORDER_LINE LON P.PART_NUM = L.PART_NUMGROUP BY P.PART_NUMORDER BY P.PART_NUM;Msg 8120, Level 16, State 1, Line 1Column 'PART.DESC' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.The PART table has columns;PART_NUMDESCON_HANDSince I'm grouping on PART_NUM I don't see why I can't also display the part description from the same row, same table.SELECT P.PART_NUM, P.DESC, P.ON_HAND, SUM(L.NUM_ORDERED)FROM PART PLEFT OUTER JOIN ORDER_LINE LON P.PART_NUM = L.PART_NUMGROUP BY P.PART_NUM, P.DESC, P.ON_HANDORDER BY P.PART_NUM;This seems to work. Perhaps the solution is always to simply dump everything into the GROUP BY line? Edited by davej
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...