#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep 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'
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0
    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)
    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?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0
    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?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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/
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo