Professional Web Applications Themes

case Q - Microsoft SQL / MS SQL Server

a big 'no' - you might get ready to hear joe celko preaches... case/when expression only returns a single value. -- -oj RAC v2.2 & QALite! http://www.rac4sql.net "Ricky" <state.mn.us> wrote in message news:049301c35c67$86a0ae10$gbl... ...

  1. #1

    Default Re: case Q

    a big 'no' - you might get ready to hear joe celko preaches... case/when
    expression only returns a single value.

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


    "Ricky" <state.mn.us> wrote in message
    news:049301c35c67$86a0ae10$gbl... 


    oj Guest

  2. #2

    Default Re: case Q

    Thanks oj. I knew it but thought may be...may be there was
    a chance....

    Is there any other way of defining an inline function
    based on parameter. i.e. depending on parameter execute
    either of the statements.

    Thanks for help.
     
    preaches... case/when [/ref]
    statements, [/ref]
    at09.datetime, [/ref]
    from 
    >
    >
    >.
    >[/ref]
    Ricky Guest

  3. #3

    Default Re: case Q

    oj,

    I have two functions, fn_Getdailydata and fn_GetHourlydata
    that access two tables table1 and table2 with daily and
    hourly resolution data.

    Table1
    tagkey, datetime, value, quality
    733,'2003-08-01 00:00:00',23.4,192
    734, '2003-08-02 00:00:00' , 34.0,192

    Table1
    733, '2003-08-01 00:00:00' 23.5,192
    733, '2003-08-01 01:00:00', 23.45,192
    733, '2003-08-01 02:00:00', 22.2,192


    These above functions take tagkey, start datetime, end
    datetime as parameter and fetch the values.

    Now, where I want to use inline function is a calculation
    function.

    Lets say I want to add Hourly and daily values for tagkey
    733. This is where resolution parameter comes into play.
    Depending on what resolution is passed I can use either
    fn_getdailydata or fn_gethourlydata and do my calculation.

    Does this explanation help to come up with an optimized
    answer?? Pls let me know

    Thanks
     
    expected result, we [/ref]
    was 
    >> preaches... case/when 
    >> statements, [/ref][/ref]
    like 
    >> at09.datetime, [/ref][/ref]
    AT09, [/ref][/ref]
    AT10, [/ref][/ref]
    AT11, [/ref][/ref]
    AT12 [/ref][/ref]
    at09.datetime = [/ref][/ref]
    order by [/ref][/ref]
    (at10.value,0) 
    >> from [/ref][/ref]
    AT09, [/ref][/ref]
    AT10, [/ref][/ref]
    AT11, [/ref][/ref]
    AT12 [/ref][/ref]
    at09.datetime = [/ref][/ref]
    order by [/ref]
    >
    >
    >.
    >[/ref]
    Ricky Guest

  4. #4

    Default Re: case Q

    The CASE expression is an *expresion* and not a control statement; that
    is, it returns a value of one datatype. SQL-92 stole the idea and the
    syntax from the ADA programming language. Here is the BNF for a <case
    specification>:

    <case specification> ::= <simple case> | <searched case>

    <simple case> ::=
    CASE <case operand>
    <simple when clause>...
    [<else clause>]
    END

    <searched case> ::=
    CASE
    <searched when clause>...
    [<else clause>]
    END

    <simple when clause> ::= WHEN <when operand> THEN <result>

    <searched when clause> ::= WHEN <search condition> THEN <result>

    <else clause> ::= ELSE <result>

    <case operand> ::= <value expression>

    <when operand> ::= <value expression>

    <result> ::= <result expression> | NULL

    <result expression> ::= <value expression>

    The searched CASE expression is probably the most used version of the
    expression. The WHEN ... THEN ... clauses are executed in left to right
    order. The first WHEN clause that tests TRUE returns the value given in
    its THEN clause. And, yes, you can nest CASE expressions inside each
    other. If no explicit ELSE clause is given for the CASE expression,
    then the database will insert a default ELSE NULL clause. If you want
    to return a NULL in a THEN clause, then you must use a CAST (NULL AS
    <datatype>) expression. I recommend always giving the ELSE clause, so
    that you can change it later when you find something explicit to return.

    The <simple case expression> is defined as a searched CASE expression in
    which all the WHEN clauses are made into equality comparisons against
    the <case operand>. For example

    CASE iso__code
    WHEN 0 THEN 'Unknown'
    WHEN 1 THEN 'Male'
    WHEN 2 THEN 'Female'
    WHEN 9 THEN 'N/A'
    ELSE NULL END

    could also be written as:

    CASE
    WHEN iso__code = 0 THEN 'Unknown'
    WHEN iso__code = 1 THEN 'Male'
    WHEN iso__code = 2 THEN 'Female'
    WHEN iso__code = 9 THEN 'N/A'
    ELSE NULL END

    There is a gimmick in this definition, however. The expression

    CASE foo
    WHEN 1 THEN 'bar'
    WHEN NULL THEN 'no bar'
    END

    becomes

    CASE WHEN foo = 1 THEN 'bar'
    WHEN foo = NULL THEN 'no_bar' -- error!
    ELSE NULL END

    The second WHEN clause is always UNKNOWN.

    The SQL-92 Standard defines other functions in terms of the CASE
    expression, which makes the language a bit more compact and easier to
    implement. For example, the COALESCE () function can be defined for one
    or two expressions by

    1) COALESCE (<value exp #1>) is equivalent to (<value exp #1>)

    2) COALESCE (<value exp #1>, <value exp #2>) is equivalent to

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE <value exp #2> END

    then we can recursively define it for (n) expressions, where (n >= 3),
    in the list by

    COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE COALESCE (<value exp #2>, . . ., n)
    END

    Likewise, NULLIF (<value exp #1>, <value exp #2>) is equivalent to:

    CASE WHEN <value exp #1> = <value exp #2>
    THEN NULL
    ELSE <value exp #1> END

    It is important to beure that you have a THEN or ELSE clause with a
    datatype that the compiler can find to determine the highest datatype
    for the expression.

    --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!
    Joe Guest

  5. #5

    Default Re: case Q

    Create a wrapper function such as...

    e.g.

    create table dbo.daily(tagkey int, dt datetime, value money, quality int)

    insert dbo.daily
    select 733,'2003-08-01 00:00:00',23.4,192
    union all select 734, '2003-08-02 00:00:00' , 34.0,192

    create table dbo.hourly(tagkey int, dt datetime, value money, quality int)
    insert dbo.hourly
    select 733, '2003-08-01 00:00:00', 23.5,192
    union all select 733, '2003-08-01 01:00:00', 23.45,192
    union all select 733, '2003-08-01 02:00:00', 22.2,192
    go
    create function dbo.fn_Getdailydata (
    tagkey int,
    dt datetime,
    value money,
    quality int)
    returns int
    as
    begin
    return(select quality*2 from dbo.daily where tagkey=tagkey and dt=dt and
    value=value and quality=quality)
    end
    go

    create function dbo.fn_GetHourlydata(
    tagkey int,
    dt datetime,
    value money,
    quality int)
    returns int
    as
    begin
    return(select quality*4 from dbo.hourly where tagkey=tagkey and dt=dt and
    value=value and quality=quality)
    end
    go

    create function dbo.fn_getData(resolution varchar(30))
    returns tb table(tagkey int, dt datetime, value money, quality int,result
    int)
    as
    begin
    if resolution='daily'
    insert tb select *,dbo.fn_Getdailydata(tagkey,dt,value,quality) from
    dbo.daily
    if resolution='hourly'
    insert tb select *,dbo.fn_GetHourlydata(tagkey,dt,value,quality) from
    dbo.hourly
    return
    end
    go

    --test run
    select *
    from dbo.fn_getData('daily')

    go
    drop function dbo.fn_getData,dbo.fn_Getdailydata,dbo.fn_GetHourl ydata
    drop table dbo.daily,dbo.hourly
    go



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


    "Ricky" <state.mn.us> wrote in message
    news:00b301c35c73$7b989ee0$gbl... 
    > expected result, we [/ref]
    > was [/ref]
    > like [/ref]
    > AT09, [/ref]
    > AT10, [/ref]
    > AT11, [/ref]
    > AT12 [/ref]
    > at09.datetime = [/ref]
    > order by [/ref]
    > (at10.value,0) [/ref]
    > AT09, [/ref]
    > AT10, [/ref]
    > AT11, [/ref]
    > AT12 [/ref]
    > at09.datetime = [/ref]
    > order by 
    > >
    > >
    > >.
    > >[/ref][/ref]


    oj Guest

  6. #6

    Default Re: case Q

    <LOL>

    --
    -oj


    "Joe Celko" <edu> wrote in message
    news:phx.gbl... 


    oj Guest

  7. #7

    Default Re: case Q

    As you said, I got preached by 'The Preacher' celko...LoL [/ref]
    statement; that [/ref]
    the idea and the [/ref]
    BNF for a <case [/ref]
    case> [/ref]
    <result> [/ref]
    THEN <result> [/ref]
    version of the [/ref]
    in left to right [/ref]
    the value given in [/ref]
    expressions inside each [/ref]
    CASE expression, [/ref]
    clause. If you want [/ref]
    CAST (NULL AS [/ref]
    ELSE clause, so [/ref]
    explicit to return. [/ref]
    CASE expression in [/ref]
    comparisons against [/ref]
    expression [/ref]
    the CASE [/ref]
    and easier to [/ref]
    be defined for one [/ref]
    exp #1>) [/ref]
    equivalent to [/ref]
    where (n >= 3), [/ref]
    equivalent to: [/ref]
    equivalent to: [/ref]
    clause with a [/ref]
    highest datatype [/ref]
    what the keys, [/ref]
    datatypes, etc. in your [/ref]
    *** 
    >
    >
    >.
    >[/ref]
    Ricky Guest

  8. #8

    Default Re: case Q

    Did they steal the word *expresion* from ADA, too?

    SK

    Joe Celko wrote:
     

    Steve Guest

  9. Moderated Post

    Default Re: case Q

    Removed by Administrator
    Delbert Guest
    Moderated Post

Similar Threads

  1. Replies: 112
    Last Post: December 9th, 05:46 PM
  2. Is there a way to convert lower case text to upper case text in PHP?
    By tanas@ing.com.au in forum PHP Development
    Replies: 3
    Last Post: December 11th, 06:12 AM
  3. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12: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