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

    Unanswered: View - how to limit hugh values returned...

    Hi all,

    I have a view which is returning hugh values for some columns, like 30 digit values. I want to limit the values like,
    If the values are getter than 1000 return ZERO other wise return the VALUE.

    Iam poor with SQL, can someone please help?

    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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This seems to ask for a CASE construct, something like
    Code:
    CASE WHEN v > 10000000000 THEN 0
         ELSE v END
    (Replace "v" with the above wherever you want to see a zero instead of a huge value.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Nov 2005
    Posts
    24
    Thanks a lot for the help.

    -Db

Posting Permissions

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