Working with Variables

Dev Shed Forums Sponsor:

August 15th, 2012, 10:29 PM

maluktuk

Registered User

Join Date: Aug 2012

Posts: 5

Time spent in forums: 1 h 4 m 31 sec
Reputation Power: 0

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

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

I assume that you expect that when using

Code:

, @i2 / @i1

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

maluktuk

Registered User

Join Date: Aug 2012

Posts: 5

Time spent in forums: 1 h 4 m 31 sec
Reputation Power: 0

Quote:

Originally Posted by swampBoogie

I assume that you expect that when using

Code:

, @i2 / @i1

it should be substituted by

Code:

ct.ordamt/(ct.ordamt - ct.costamt)

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

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,357

Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 23 sec
Reputation Power: 4140

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.com | rudy.ca
please visit Simply SQL and buy my book

August 16th, 2012, 10:00 PM

maluktuk

Registered User

Join Date: Aug 2012

Posts: 5

Time spent in forums: 1 h 4 m 31 sec
Reputation Power: 0

Quote:

Originally Posted by r937

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

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,357

Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 23 sec
Reputation Power: 4140

Quote:

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

Quote:

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/

Working with Variables

Developer Shed Advertisers and Affiliates