Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    15

    Unanswered: Use alias for expression

    Hi all!
    I'm quite new to Sybase, so I have one question.

    For example, I have a table called Actions:
    Id … StartTime EndTime TotalRows CurrentRow
    1 … 01.07.2012 1:10:18 01.07.2012 9:33:45 728009 728009 //Process finished
    2 … 03.07.2012 11:46:53 03.07.2012 16:08:36 728983 519331 //Process is running

    Where ‘EndTime’ means the time of computing the last row. And the number of this row is stored in the CurrentRow column.
    This table is updated dynamically, when some action occurs.

    So my Select statement looks like:

    Select ac.*,
    convert(varchar, round((convert(float,ac.CurrentRow)/nullif(convert(float,ac.TotalRows), 0))*100, 2)) || (case when ac.CurrentRow is null then '' else '%' end) as 'Percentage done',
    round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2) as 'Speed (Actions/s)',
    nullif(convert(float,(ac.TotalRows - ac.CurrentRow)), 0)/nullif(round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2), 0) as ‘TotalSecRemaning’,
    floor(nullif(convert(float,(ac.TotalRows - ac.CurrentRow)), 0)/nullif(round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2), 0) / 3600) as ‘Hours remaining’,
    floor(nullif(convert(float,(ac.TotalRows - ac.CurrentRow)), 0)/nullif(round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2), 0) / 60) % 60 as ‘Min remaining’,
    nullif(convert(float,(ac.TotalRows - ac.CurrentRow)), 0)/nullif(round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2), 0) % 60 as ‘Sec remaining’,
    From Actions ac
    So my question – is it somehow possible to use alias for column ‘TotalSecRemaning’ in later expressions? So I could use it’s alias (ex. floor(‘TotalSecRemaning’ / 3600) as 'Hours remaning' ) instead of formula:floor(nullif(convert(float,(ac.TotalRows - ac.CurrentRow)), 0)/nullif(round(convert(float,ac.CurrentRow)/nullif(convert(float,datediff(ss, ac.StartTime, ac.EndTime)), 0), 2), 0) / 3600) as ‘Hours remaining’?
    Last edited by CbIP; 07-04-12 at 01:08.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No, use a view/derived-table

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    e.g.
    Code:
    SELECT *,
      floor(TotalSecRemaning/ 3600) as 'Hours remaining',
      floor(TotalSecRemaning/ 60) % 60 as 'Min remaining',
      TotalSecRemaning % 60 as 'Sec remaining'
    FROM 
      (SELECT *,
         nullif(TotalRows - CurrentRow, 0) / [Speed (Actions/s)] as 'TotalSecRemaning'
       FROM 
         (Select ac.*,
            str(100e*ac.CurrentRow/nullif(ac.TotalRows,0),6,2) + case when ac.CurrentRow is null then '' else '%' end as 'Percentage done',
            nullif(round(1e*ac.CurrentRow/nullif(datediff(ss, ac.StartTime, ac.EndTime), 0), 2), 0) as 'Speed (Actions/s)'
          FROM Actions ac
         )view1
      )view2
    Last edited by pdreyer; 07-06-12 at 09:04.

  4. #4
    Join Date
    Jul 2012
    Posts
    15
    OK. Thanks for help!

Posting Permissions

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