Professional Web Applications Themes

Newbie SQL programming question - Microsoft SQL / MS SQL Server

Try to write a sql statement that I didn't have any problem with in Access. Table: Test Fields: Rec_Type, Amount Need to write an "IF" Function that says something like IF Rec_Type = 1 then Amount else 0 BUT, I can't have the Rec_Type in the Select Statement. So I tryed, Select Case when test.rec_type=1 then test.amount else 0 end From dbo.test But I get an error stating that I can't use Rec_Type because it is not contained in the Select Statement. In access this wasn't a problem Any help would be greatly appreciated THANK YOU!!!!...

  1. #1

    Default Newbie SQL programming question

    Try to write a sql statement that I didn't have any
    problem with in Access.

    Table: Test
    Fields: Rec_Type, Amount

    Need to write an "IF" Function that says something like IF
    Rec_Type = 1 then Amount else 0

    BUT, I can't have the Rec_Type in the Select Statement.

    So I tryed,

    Select Case when test.rec_type=1 then test.amount else 0
    end
    From dbo.test

    But I get an error stating that I can't use Rec_Type
    because it is not contained in the Select Statement.

    In access this wasn't a problem

    Any help would be greatly appreciated

    THANK YOU!!!!
    graham Guest

  2. #2

    Default Re: Newbie SQL programming question

    Graham,
    Try this
    begin transaction
    create table Test (Rec_type int, Amount money)
    insert into test(Rec_type ,Amount) values(1,34)
    insert into test(Rec_type ,Amount) values(1,324)
    insert into test(Rec_type ,Amount) values(1,314)
    insert into test(Rec_type ,Amount) values(0,34)
    insert into test(Rec_type ,Amount) values(0,324)
    insert into test(Rec_type ,Amount) values(0,314)

    select case rec_type when 1 then amount else 0 end AM from test

    rollback tran

    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    http://www.ag-software.com/ags_scribe_index.asp

    "graham" <org> wrote in message
    news:086c01c350e2$cc22a780$gbl... 


    Greg Guest

  3. #3

    Default Re: Newbie SQL programming question

    Sorry, your right that is valid, THIS is not

    Select Case when Test.Rec_Type=1 then SUM(Test.Amount)
    else 0 end as [Amount]
    From dbo.Test

    I guess my problem is that I need to aggregate the Amount
    field for all records where Rec_Type=1

    Thanks again

     
    case-sensitivity. 
    end as [Amount] [/ref]
    IF 
    >
    >
    >.
    >[/ref]
    graham Guest

  4. #4

    Default Re: Newbie SQL programming question

    graham wrote: 

    Select
    SUM(Case when Test.Rec_Type=1
    then Test.Amount
    else 0 end) as [Amount]
    From dbo.Test

    Dieter

    Dieter Guest

  5. #5

    Default Re: Newbie SQL programming question

    THANKS, I GOT IT,

    I need the SUM statement BEFORE the Case, not within it.. 
    case-sensitivity. 
    end as [Amount] [/ref]
    IF 
    >
    >
    >.
    >[/ref]
    gv Guest

Similar Threads

  1. Newbie to programming, Net::Divert, Linux-Debian, autoflush error
    By vivek.m1234@yahoo.com in forum PERL Modules
    Replies: 4
    Last Post: April 26th, 10:42 AM
  2. clueless newbie to web programming
    By John David Adamski in forum PERL Modules
    Replies: 4
    Last Post: February 20th, 10:01 AM
  3. A Newbie's Question about Unix Programming Examples
    By Haoyu in forum UNIX Programming
    Replies: 2
    Last Post: October 23rd, 05:04 AM
  4. newbie to network programming
    By Dave Hughes in forum UNIX Programming
    Replies: 5
    Last Post: August 1st, 12:41 AM
  5. Unix Systems Programming Newbie - exec format error
    By Gabriel in forum UNIX Programming
    Replies: 8
    Last Post: July 17th, 03:02 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