Following is a query I am trying to build to supply data to a pie chart. The pie chart is supposed to indicate the percent scrap of the current order. I just implemented this and found out that there are errors during a changeover.
Query help is needed because sometimes during a changeover there are no records to return and as a result it gives me a null pointer error in the pie chart. Most other areas I can define a fall back value but this control does not provide this option. (not sure how you would define a fallback for a dataset )
I need this query to return two rows of data.
Row 0 = weight where ship = n (color is red)
Row 1 = weight where ship = y (color is green)
If an order is just started there no rows at all returned. In this case one row is OK since the pie chart will be solid red.
If the order has rows but they are all the same type then it might need another row because the color may be wrong. (The pie charts color is by position in the dataset.
Below where @count = 1 is where I need help.
This is what I have so far.
[code]DECLARE @count AS int, @order AS int, @line as int;
SET @order = {Root Container.cntStatus.Container.L1_OrderNum};
SET @line = {Root Container.cntStatus.Container.L1_OrderLineNum};
SET @count =
(SELECT COUNT(*)
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line));
IF (@count = 0) # This is working OK
BEGIN
SELECT top 1 ‘n’ AS ship, 0 AS val
FROM CurrentIndexDetail
END
IF (@count = 1) # HELP, need to insert another row of fabricated data in the right order
BEGIN
SELECT Ship, SUM(EstimatedWeight)
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line)
GROUP BY Ship
ORDER BY Ship DESC
END
IF (@count > 1) # normal operation
BEGIN
SELECT Ship, SUM(EstimatedWeight)
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line)
GROUP BY Ship
ORDER BY Ship DESC
END[/code]
Would something like this work? Still trying to wrap my head around the finer points of SQL, so be kind…
It should add a row of data with no actual weight to it, so the ‘main’ query would take take care of the rest. In fact, you could even pare it down so that it only inserts the row for the condition that needs it, since the opposite condition would already have the correct color…
IF (@count = 1) # HELP, need to insert another row of fabricated data in the right order
BEGIN
IF (SELECT top 1 SHIP FROM CurrentIndexDetail) ='n'
BEGIN
INSERT INTO CurrentIndexDetail (OrderNumber, OrderLineNumber, Ship, EstimatedWeight)
VALUES (@order, @line, 'y', 0)
END
ELSE
BEGIN
INSERT INTO CurrentIndexDetail (OrderNumber, OrderLineNumber, Ship, EstimatedWeight)
VALUES (@order, @line, 'n', 0)
END
SELECT Ship, SUM(EstimatedWeight)
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line)
GROUP BY Ship
ORDER BY Ship DESC
END
Carl, I just had another thought: is it possible to set the PieChart color through scripting? Maybe set the color of the first section according to whatever’s in the first row of the dataset…
I ran into a few more snags. I was charting the percentage of scrap but there were cases where the status was undefined and the ‘ship’ column was either a space or a null. I cannot add to the table, only read from it. The data is pushed to SQL from Access and will be overwritten if I write to it.
Here is the query I ended up with.
[code]DECLARE @count AS int, @order AS int, @line as int;
SET @order = {Root Container.cntStatus.Container.L1_OrderNum};
SET @line = {Root Container.cntStatus.Container.L1_OrderLineNum};
SET @count =
(SELECT COUNT(DISTINCT Ship)
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line));
IF (@count = 0)
BEGIN
SELECT ‘n’ AS ship0, 0 AS val
FROM CurrentIndexDetail
UNION
SELECT ‘y’ AS ship0, 0 AS val
FROM CurrentIndexDetail
END
IF (@count = 1)
BEGIN
SELECT Ship, sum(EstimatedWeight) as val
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line)
GROUP BY Ship
UNION
SELECT ‘y’ AS Ship, 0 AS val
FROM CurrentIndexDetail
ORDER BY Ship DESC
END
IF (@count > 1)
BEGIN
SELECT
(CASE
WHEN (Ship = ‘n’) THEN ‘Scrap’
WHEN (Ship = ‘N’) THEN ‘Scrap’
WHEN (Ship = ‘y’) THEN ‘Shippable’
WHEN (Ship = ‘Y’) THEN ‘Shippable’
WHEN (Ship = ’ ') THEN ‘a space’
WHEN (Ship is null) THEN ‘a null’
END) as ship,
SUM(EstimatedWeight) as val
FROM CurrentIndexDetail
WHERE (OrderNumber = @order) AND (OrderLineNumber = @line)
GROUP BY Ship
ORDER BY Ship DESC
END[/code]
The UNION did the trick. I have read about it but this is the first time I ever used it.
Thanks again,
)