August 15th, 2012, 10:29 PM
-
Working with Variables
I'm trying my hand at using variables, and unfortunately I think I'm missing a key principle somewhere. The following query worked before I tried to declare, set, and then use variables to calculate a margin percentage. Is there a glaring error in this that, once corrected, will make this work as desired?
Thanks in advance for the help.
Code:
DECLARE @i1 decimal(10,2)
DECLARE @i2 decimal(10,2)
SET @i1 = ct.ordamt
SET @i2 = (ct.ordamt - ct.costamt)
SELECT COUNT (ct.ordno) AS "# of Orders"
, SUM (ct.ordamt) AS "Total"
, SUM (ct.ordamt - ct.costamt) AS "Margin"
, @i2 / @i1 AS "Margin %"
FROM ordrhist AS ct
WHERE
ct.orddate = curdate() - 0 AND SUBSTRING(ct.status,1,1)<>'C'AND SUBSTRING(ct.status,2,1)='O' AND ct.hold <> 'Y' AND ct.complete <> 'I'
August 16th, 2012, 04:09 AM
-
I assume that you expect that when using
it should be substituted by
Code:
ct.ordamt/(ct.ordamt - ct.costamt)
when executing the select statement. Variables does not work that way. Variables can only hold values, e.g. strings and integer, not a column reference. I think that attempting to use variables that way, if that is your intention, only makes the code less readable.
August 16th, 2012, 08:32 AM
-
Originally Posted by swampBoogie
I assume that you expect that when using
it should be substituted by
Code:
ct.ordamt/(ct.ordamt - ct.costamt)
when executing the select statement. Variables does not work that way. Variables can only hold values, e.g. strings and integer, not a column reference. I think that attempting to use variables that way, if that is your intention, only makes the code less readable.
SwampBoogie,
You assumed correctly. Thanks for letting me know about variables -- I'm definitely new to this and am grateful to learn everything I can. I had originally tried something like that before but I kept getting an error. This is what I was trying:
Code:
SELECT COUNT (ct.ordno) AS "# of Orders"
, SUM (ct.ordamt) AS "Total"
, SUM (ct.ordamt - ct.costamt) AS "Margin"
, (ct.ordamt - ct.costamt) / ct.ordamt AS "Margin %"
FROM ordrhist AS ct
WHERE
ct.orddate = curdate() - 0 AND SUBSTRING(ct.status,1,1)<>'C'AND SUBSTRING(ct.status,2,1)='O' AND ct.hold <> 'Y' AND ct.complete <> 'I'
If I leave out the third SELECT statement, everything works fine. It's when I try to calculate a margin percentage that I get an error. Any thoughts?
August 16th, 2012, 09:09 AM
-
the problem is, when you assign a column alias to an expression (like your two SUMs), you are not allowed to reference those column aliases in the same SELECT clause
two workarounds --
1. repeat the expressions
Code:
SELECT COUNT(*) AS "# of Orders"
, SUM(ct.ordamt) AS "Total"
, SUM(ct.ordamt - ct.costamt) AS "Margin"
, ( SUM(ct.ordamt) -
SUM(ct.ordamt - ct.costamt) ) /
SUM(ct.ordamt) AS "Margin %"
FROM ordrhist AS ct
WHERE ct.orddate = curdate() - 0
AND SUBSTRING(ct.status,1,1)<>'C'
AND SUBSTRING(ct.status,2,1)='O'
AND ct.hold <> 'Y'
AND ct.complete <> 'I'
2. push the query down into a subquery as a derived table
Code:
SELECT "# of Orders"
, "Total"
, "Margin"
, ( "Total" - "Margin" ) / "Total" AS "Margin %"
FROM ( SELECT COUNT(*) AS "# of Orders"
, SUM(ct.ordamt) AS "Total"
, SUM(ct.ordamt - ct.costamt) AS "Margin"
FROM ordrhist AS ct
WHERE ct.orddate = curdate() - 0
AND SUBSTRING(ct.status,1,1)<>'C'
AND SUBSTRING(ct.status,2,1)='O'
AND ct.hold <> 'Y'
AND ct.complete <> 'I' ) AS dt
August 16th, 2012, 10:00 PM
-
Originally Posted by r937
the problem is, when you assign a column alias to an expression (like your two SUMs), you are not allowed to reference those column aliases in the same SELECT clause
two workarounds --
1. repeat the expressions
Code:
SELECT COUNT(*) AS "# of Orders"
, SUM(ct.ordamt) AS "Total"
, SUM(ct.ordamt - ct.costamt) AS "Margin"
, ( SUM(ct.ordamt) -
SUM(ct.ordamt - ct.costamt) ) /
SUM(ct.ordamt) AS "Margin %"
FROM ordrhist AS ct
WHERE ct.orddate = curdate() - 0
AND SUBSTRING(ct.status,1,1)<>'C'
AND SUBSTRING(ct.status,2,1)='O'
AND ct.hold <> 'Y'
AND ct.complete <> 'I'
2. push the query down into a subquery as a derived table
Code:
SELECT "# of Orders"
, "Total"
, "Margin"
, ( "Total" - "Margin" ) / "Total" AS "Margin %"
FROM ( SELECT COUNT(*) AS "# of Orders"
, SUM(ct.ordamt) AS "Total"
, SUM(ct.ordamt - ct.costamt) AS "Margin"
FROM ordrhist AS ct
WHERE ct.orddate = curdate() - 0
AND SUBSTRING(ct.status,1,1)<>'C'
AND SUBSTRING(ct.status,2,1)='O'
AND ct.hold <> 'Y'
AND ct.complete <> 'I' ) AS dt
r937,
Thanks again for the help. The first one returned an error (I suspect an issue with the program I'm using), but the second one worked like a charm! That's actually what I had in mind in my original request but did such a poor job of verbalizing. I'm hoping to pick up more of this SQL lingo.
Is there a simple way to add a thousand separator to the "Total" and "Margin" amounts and limit the "Margin %" to two decimal points?
PS - I tried finding your book through the link in your signature? Is that still available, and would it be a good resource for a newb like me?
August 17th, 2012, 03:34 AM
-
Originally Posted by maluktuk
Is there a simple way to add a thousand separator to the "Total" and "Margin" amounts and limit the "Margin %" to two decimal points?
FORMAT() -- if you're on SQL Server 2012
otherwise, it's better to do this in the front-end application
Originally Posted by maluktuk
PS - I tried finding your book through the link in your signature? Is that still available, and would it be a good resource for a newb like me?
yes it's still available, and yes, you might benefit from it
the reason there's no link is too long a story to go into now
you can get it from amazon or from http://www.sitepoint.com/books/sql1/