Roll it up just like a Burito
-
i am tring to get a better understanding on how this roll up works.. One of my questins is that; in the case statement below it says ' WHEN 0 THEN '.
What is that talking about? when? what? becomes 0?
Thanks,
Erik....
SELECT
O.customerid as MyCustomerID,
CASE
GROUPING (od.orderid)WHEN 0 THEN od.orderid ELSE '999999999' END AS MyOrderID,
SUM (od.quantity * od.unitprice) as [ORDER AMOUNT]
FROM Orders o, [Order Details] od
WHERE Year(orderdate)= 1997 AND od.orderid=o.orderid
Group BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, MyOrderID
Dam again!
-
Eric - did you "drop by" BOL with this question -
Here's an explanation of what the "Grouping" function does...
"Grouping is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP."
In your T-Sql then - the query is saying that if the orderid is not a result of the rollup, then display the orderid else '9999999999' - though maybe someone wanted it the other way around...?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the help!
I am using the roll up function to compute totals and seperate inforamtion .
Example:
In my code i can use the roll up fucntion to return prestructured totals..
On the 99999999 line i will...
Dim drv as DatarowView = New ds.tables(0).defaultview
if drv("MyCustomerID") = 99999999 then
e.item.cell.removeAt(0)
e.item.cell.backcolor = color.Yellow... ect...
ECT...
For the datagrid or the datalist..
So for this one row with a predefined results set i can make each total row a different color and .removeAt(1)...i will remove the 99999999
Dam again!
-
Whats a BOL?
Dam again!
-
Books online.
-
o ok..
i look their and was not fruitful in my search
Dam again!
-
Now that i am looking at this again, i do not see an INNER JOIN in the querry.
Question: Does the grouping function take the place of the inner join, (in some way)
SELECT
O.customerid as MyCustomerID,
CASE
GROUPING (od.orderid)WHEN 0 THEN od.orderid ELSE '999999999' END AS MyOrderID,
SUM (od.quantity * od.unitprice) as [ORDER AMOUNT]
FROM Orders o, [Order Details] od
WHERE Year(orderdate)= 1997 AND od.orderid=o.orderid
Group BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, MyOrderID
Dam again!
-
Erik - there is a join in your query....in the where clause....
..WHERE Year(orderdate)= 1997 AND od.orderid = o.orderid...
However, joins with this syntax are all but obsolete...read up in BOL on topic
Specifying Joins in FROM or WHERE Clauses
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply