Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    24

    Unanswered: help with if, else statement........

    Hi,

    I have a view that needs to be modified to bring back values which are less than 1000, and bring zero for all values that are greater than 1000.

    Iam new to SQL, can somebody please help with the if and else statement.


    CREATE VIEW dbo.[LT-4D3]
    AS
    SELECT TOP 100 PERCENT ReadMeterIP AS IP,
    ReadDateTime AS Read_DateTime,
    CAST(CONVERT(CHAR(10),ReadDateTime,101)as datetime) AS Read_Date,
    CONVERT(CHAR(2),ReadDateTime,8)+1 AS Read_Hour,
    SUBSTRING(CONVERT(CHAR(8),ReadDateTime,8),4,2)+1 AS Read_Minute,
    SUBSTRING(CONVERT(CHAR(8),ReadDateTime,8),7,2)+0 AS Read_Second,
    ch221 AS D1,
    ch222 AS D2,
    ch223 AS D3,
    ch224 AS D4,
    ch231 AS R1,
    ch232 AS R2,
    ch233 AS R3,
    ch234 AS R4,
    (ch221 + ch222 + ch223 + ch224) / 4 AS [Average Discharge],
    (ch231 + ch232 + ch233 + ch234) / 4 AS [Average Return],
    ch229 AS SF1,
    ch230 AS LF1,
    ch229 - ch230 AS Superheat,
    ch231 - ch221 AS RD1,
    ch232 - ch222 AS RD2,
    ch233 - ch223 AS RD3,
    ch234 - ch224 AS RD4
    FROM ICDBUser.MeterReads
    WHERE (ReadMeterIP = '10.55.100.31')
    ORDER BY ReadDateTime

    Thanks,
    Db.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Not sure what you mean. Which values - which column(s)? Do you mean you want to display numbers, substituting 0 for those over a thousand or do you want to reduce the number of rows (only include those with "values" less than a thousand)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about some table DDL, sample data and the expected results. Read the sticky at the top of the forum for examples of what I mean.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2005
    Posts
    24
    Iam sorry. let me re-phrase.

    Some columns in the view are returning a very hugh value, like 30 digit numbers.I want to limit the result,...so that if the value is greater than 1000 return "0" other wise return the value.

    I can get you the resultset, but not immediately as iam at home.

    Please let me know if you need either the resultset or the table ddl,....to help me out.

    Thanks,
    Db.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    CASE is all you'll need then.

    Code:
    CREATE VIEW dbo.[LT-4D3]
    AS
    SELECT TOP 100 PERCENT ReadMeterIP AS IP,
    .....
    CASE WHEN ch221 > 1000 THEN 0 ELSE ch221 END AS D1, 
    ......
    --Repeat
    .....
    FROM ICDBUser.MeterReads
    WHERE (ReadMeterIP = '10.55.100.31')
    ORDER BY ReadDateTime
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - are these columns numeric?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Posts
    24
    Yes, the columns are numeric.

    Thanks a lot for the help.

    -Db

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They can't be a numeric number if they are 30 digits

    Please, oh, please read the sticky at the top of the forum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    They can't be a numeric number if they are 30 digits
    Actually they can, try this
    create table #t1 (f1 decimal(30,0), f2 numeric(30,0))
    insert into #t1 values (123456789012345678901234567890
    ,123456789012345678901234567890)
    select * from #t1
    drop table #t1

Posting Permissions

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