Professional Web Applications Themes

Select Statement Question (Again) - Microsoft SQL / MS SQL Server

I have the following simple Select statement which produces an error when I attempt to save it as part of a stored proc. Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as AgedBalance From Invoices Where AgedBalance > MinimumBalance Note: dbo.Customer_CalculateAgedBalance is a UDF. It produces an error because 'AgedBalance' is an invalid column name. My question is this. I would like to include the value contained in 'AgedBalance' in my Where clause without having to call the UDF a second time. Is there anyway I can do this? Thanks....

  1. #1

    Default Select Statement Question (Again)

    I have the following simple Select statement which produces an error when I
    attempt to save it as part of a stored proc.

    Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as
    AgedBalance
    From Invoices
    Where AgedBalance > MinimumBalance

    Note: dbo.Customer_CalculateAgedBalance is a UDF.

    It produces an error because 'AgedBalance' is an invalid column name. My
    question is this. I would like to include the value contained in
    'AgedBalance' in my Where clause without having to call the UDF a second
    time. Is there anyway I can do this?

    Thanks.



    Largo SQL Tools Guest

  2. #2

    Default Re: Select Statement Question (Again)

    Hi

    Try creating a view!

    For BOL or
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_select_clause.asp[/url]

    "column_alias can be used in an ORDER BY clause, but it cannot be used in a
    WHERE, GROUP BY, or HAVING clause. "

    John

    "Largo SQL Tools" <nospam> wrote in message
    news:uw2QTMISDHA.3192tk2msftngp13.phx.gbl...
    > I have the following simple Select statement which produces an error when
    I
    > attempt to save it as part of a stored proc.
    >
    > Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as
    > AgedBalance
    > From Invoices
    > Where AgedBalance > MinimumBalance
    >
    > Note: dbo.Customer_CalculateAgedBalance is a UDF.
    >
    > It produces an error because 'AgedBalance' is an invalid column name. My
    > question is this. I would like to include the value contained in
    > 'AgedBalance' in my Where clause without having to call the UDF a second
    > time. Is there anyway I can do this?
    >
    > Thanks.
    >
    >
    >

    John Bell Guest

  3. #3

    Default Re: Select Statement Question (Again)

    Or you could use a derived table, for example

    SEELCT I.InvoiceID, I.AgedBalance
    FROM (
    Select InvoiceID
    , dbo.Customer_CalculateAgedBalance (CustomerID) as AgedBalance
    From Invoices
    ) AS I
    WHERE I.AgedBalance > MinimumBalance

    Gert-Jan


    Largo SQL Tools wrote:
    >
    > I have the following simple Select statement which produces an error when I
    > attempt to save it as part of a stored proc.
    >
    > Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as
    > AgedBalance
    > From Invoices
    > Where AgedBalance > MinimumBalance
    >
    > Note: dbo.Customer_CalculateAgedBalance is a UDF.
    >
    > It produces an error because 'AgedBalance' is an invalid column name. My
    > question is this. I would like to include the value contained in
    > 'AgedBalance' in my Where clause without having to call the UDF a second
    > time. Is there anyway I can do this?
    >
    > Thanks.
    Gert-Jan Strik Guest

  4. #4

    Default Re: Select Statement Question (Again)

    The only place you can use an alias directly is in the Order By clause. In
    your case, deriving the query should do it.

    select *
    from (
    Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as
    AgedBalance
    From Invoices)x
    Where AgedBalance > MinimumBalance


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "Largo SQL Tools" <nospam> wrote in message
    news:uw2QTMISDHA.3192tk2msftngp13.phx.gbl...
    > I have the following simple Select statement which produces an error when
    I
    > attempt to save it as part of a stored proc.
    >
    > Select InvoiceID, dbo.Customer_CalculateAgedBalance (CustomerID) as
    > AgedBalance
    > From Invoices
    > Where AgedBalance > MinimumBalance
    >
    > Note: dbo.Customer_CalculateAgedBalance is a UDF.
    >
    > It produces an error because 'AgedBalance' is an invalid column name. My
    > question is this. I would like to include the value contained in
    > 'AgedBalance' in my Where clause without having to call the UDF a second
    > time. Is there anyway I can do this?
    >
    > Thanks.
    >
    >
    >

    oj Guest

  5. #5

    Default Re: Select Statement Question (Again)

    >> I would like to include the value contained in 'AgedBalance' in my
    Where clause without having to call the UDF a second time. <<

    You have a FUNDAMENTAL misunderstanding of how SQL works.

    Here is how a SELECT works in SQL ... at least in theory. Real
    products will optimize things when they can.

    a) Start in the FROM clause and build a working table from all of the
    joins, unions, intersections, and whatever other table constructors
    are there. The table expression> AS <correlation name> option allows
    you give a name to this working table which you then have to use for
    the rest of the containing query.

    b) Go to the WHERE clause and remove rows that do not pass criteria;
    that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
    WHERE clause is applied to the working in the FROM clause.

    c) Go to the optional GROUP BY clause, make groups and reduce each
    group to a single row, replacing the original working table with the
    new grouped table. The rows of a grouped table must be group
    characteristics: (1) a grouping column (2) a statistic about the group
    (i.e. aggregate functions) (3) a function or (4) an expression made up
    of the those three items.

    d) Go to the optional HAVING clause and apply it against the grouped
    working table; if there was no GROUP BY clause, treat the entire table
    as one group.

    e) Go to the SELECT clause and construct the expressions in the list.
    This means that the scalar subqueries, function calls and expressions
    in the SELECT are done after all the other clauses are done. The AS
    operator can give a name to expressions in the SELECT list, too.
    These new names come into existence all at once, but after the WHERE
    clause, GROUP BY clause and HAVING clause has been executed; you
    cannot use them in the SELECT list or the WHERE clause for that
    reason.

    If there is a SELECT DISTINCT, then redundant duplicate rows are
    removed. For purposes of defining a duplicate row, NULLs are treated
    as matching (just like in the GROUP BY).

    f) Nested query expressions follow the usual scoping rules you would
    expect from a block structured language like C, Pascal, Algol, etc.
    Namely, the innermost queries can reference columns and tables in the
    queries in which they are contained.

    You use a VIEW or a derived table, or you can make two calls; the
    optimizer should realize that this is a deterministic function and
    execute it only once per row. Or you can expand the code for the
    proprietary, non-portable UDF in the statement and use one or more of
    the underlying columns (we can only guess at what the code does). A
    UDF also will block the use of indexes, so code expansion might be
    faster.
    --CELKO-- Guest

  6. #6

    Default Re: Select Statement Question (Again)

    I think you have a fundemental misunderstanding as to the syntax of a UDF
    within SQL Server.

    This news group is for MICROSOFT SQL SERVER and not a PURE SET BASED THEORY
    group - perhaps you should remember that!

    SQL Server 2000 has what are called UDF's (User Defined Functions), they can
    be called in various guises, this particular method returns a constant,
    whilst they are properitary they offer massive power and flexibility within
    the real world application development.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  7. #7

    Default Re: Select Statement Question (Again)

    Tony,

    Thank you. I would have said this but it probably would have sounded like
    "sour grapes" coming from the one who posted the question. Frankly, I could
    care less what is portable and what isn't. My world, and the projects I
    manage, revolve around Microsoft. As far as I'm concerned, Microsoft IS the
    standard. It's my responsibility to fully take advantage of what SQL
    Server.

    J.R.
    Largo SQL Tools
    The Finest Collection of SQL Tools Available
    http://www.largosqltools.com


    "Tony Rogerson" <eu.com> wrote in message
    news:phx.gbl... 
    THEORY 
    can 
    within 


    Largo Guest

  8. #8

    Default Re: Select Statement Question (Again)

    Mr. Celko,

    "you'll find rookie programmers who write their code with proprietary
    features of whatever SQL product they have. "

    Interesting. Here is my definition of what a rookie programmer would do.
    Rookie programmer is told by his boss that he needs to create a database to
    support a new application the company is creating. The boss makes it clear
    it is a Microsoft only application and he should take advantage of all the
    features of SQL Server 2000 in order to optimize it for SQL Server. Rookie
    programmer reads Mr. Celko's article and decides that the boss is being
    short sighted and thinks he knows more than management about the target
    market for this app. So, rookie programmer decides to create the database
    using no UDF's, and of course, no T-SQL code because that isn't portable.
    Boss finds out what rookie programmer did and rookie programmer is fired.


    "Joe Celko" <com> wrote in message
    news:OP$phx.gbl... [/ref]
    > and the projects I manage, revolve around Microsoft. As far as I'm
    > concerned, Microsoft IS the standard. <<
    >
    > You might to look up a column I wrote:
    >
    > (http://www.intelligententerprise.com/030422/607celko1_1.shtml)
    > ===============
    > I Will Never Have To Port This Code
    > Debunking shortcuts and SQL myths
    > by Joe Celko
    >
    > If you hang around the database developer newsgroups, you'll find rookie
    > programmers who write their code with proprietary features of whatever
    > SQL product they have. They argue that they'll never have to port this
    > code, so why bother to write standard SQL when you can gain some
    > advantage in performance with a proprietary feature?
    >
    > What's wrong with this thinking?
    >
    > First of all, unless you throw out a program within a year of writing
    > it, it's either going to be ported or so seriously maintained it might
    > as well be a port; and I don't mean that it's necessarily going to be
    > moved to a totally different database product.
    >
    > Moving to another release of the same product is enough to cause
    > problems. A prime example was the introduction of the SQL-92 [LEFT |
    > RIGHT | FULL] OUTER JOIN syntax to SQL products. Previously, each vendor
    > had its own version of an outer join, usually based on an "extended
    > equality" notation. Consider Chris Date's sample SELECT statement
    > ("Watch out for Outer Join," InfoDB, Spring/Summer 1990): SELECT * FROM
    > Supplier, SupParts WHERE Supplier.supno *= SupParts.supno AND qty < 200;
    > The results differ depending on the order in which the two predicates
    > are applied.
    >
    > Data Types
    >
    > Another common vendor extension is binary data or BIT data types. This
    > data type exists in the SQL-92 Standard but has no operators or rules
    > for use with a host language.
    >
    > Amateurs will use this data type for storing Boolean flags because
    > that's how they did it in a low-level programming language like C or
    > assembly. They knew that their language used 1 or 0 or -1 for TRUE and
    > something else for FALSE all the time. They didn't have to worry about
    > binary storage in the hardware being high end or low end because the
    > compiler was written for one family of machines.
    >
    > In low-level languages, the data and the code are part of the same
    > system. In a database, you can split data from any particular host
    > language so you can use it with any host language. Maybe that host
    > language doesn't even exist yet - do you know anyone who planned for C#
    > front ends five years ago? Even two years ago?
    >
    > Learn From Your Mistakes
    >
    > In the SQL Server Programming newsgroup, Tibor Karaszi wrote that he
    > used the BIT data type quite a lot in a product-specific maintenance
    > tool that will "never" be ported to another DBMS. But during the last
    > rework of the product (with approximately 60 percent rewrite), he and
    > his programmers saw the phrase "host language does not agree" quite a
    > few times.
    >
    > If 'T' and 'F' for Booleans had been used instead, Tibor infers fewer
    > bugs would have resulted during development. He also reports the same
    > experience when using bits in a binary string to report outcome of an
    > execution. The practice is to set a bit in the binary string to
    > indicate a state, such as 1 = error, 2 = warning, 4 = information, 8 =
    > in progress, so 3 indicates both errors and warnings. During this
    > rework, the code became harder to write, understand, and maintain.
    >
    > Aaron Bertrand, SQL Server MVP and frequent contributor to the same
    > newsgroup wrote this wonderful quote: "I've found that another release
    > of the same database, specifically SQL Server, is very much backward
    > compatible. From what I've seen, most problems people have when moving
    > from [versions] 6.5 to 2000 is when they try to use new features that
    > weren't supported in 6.5, but haven't upgraded their compatibility level
    > from 6.5 to 8.0 - so, through non-action, they have inadvertently made
    > it non-forward-compatible."
    >
    > The best way to find proprietary coding tricks is to move the data into
    > a data warehouse. There, data must be converted to the correct data
    > types. Now we find out that the same program running on different
    > machines has been configured with different upper- and lowercase sorting
    > rules, different display formats for temporal data, and different ANSI
    > feature settings. But it's still the same program.
    >
    > You might want to look at a wonderful feature called a "FIPS Flagger"
    > that comes with most databases. Required by the Federal Information
    > Processing Standards, it sends out a warning when proprietary code is
    > pd. It's the one configuration flag you should have set.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    Amos Guest

  9. #9

    Default Re: Select Statement Question (Again)

    Not exactly written by somebody who is neutral, heavily - and i mean heavily
    biased toward probably a fraction of a percent of people who develop against
    SQL Server who need portability.

    The article shows an overwelming arrogance toward people within this
    industry and it also shows a very outdated way of thinking.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  10. #10

    Default Re: Select Statement Question (Again)

    Not many people do care what portability is - they chose SQL Server and want
    to get the most out of the product that they've just spent their hard earned
    cash on.

    Does portability give you the edge you need in business now? I very much
    doubt it - it only adds to the development times and complexity giving rise
    to more testing, higher resource, bigger budgets etc...

    I know if I was faced with a decision to use a UDF compared to writing
    portable SQL which may well take a week longer to develop and probably
    incurr the use of some 3 or 4GL I'd take the UDF anytime.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. Select Distinct Statement Help
    By Ralph Freshour in forum PHP Development
    Replies: 5
    Last Post: August 20th, 07:27 AM
  3. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  4. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  5. Getting column name in Select statement
    By Jane in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139