1 Reply Latest reply on Jul 19, 2019 5:55 PM by mesverrum

    error with case statement

    bvaughan7

      I'm having issues with case statement, error says "no viable alternative at input 'From' in Select clause'..if anyone can assist it would be greatly appreciated..thanks

       

      select top 100 from(

           select nodes.statusled,nodes.caption,

           '/orion/netperfmon/nodedetails.aspx?netobject=n:' + cast(nodes.nodeid as varchar(5)) as detailsurl,

           nodes.nodeid,starttime.message,starttime.eventtime as downtime,

           case when(

                          select top 1 eventtime from events as endtimetable

                          where endtimetable.eventtime > starttime.eventtime

                          and endtimetable.eventtype = 5001

                          and endtimetable.message like 'bgppeerstate is%'

                          and endtimetable.networknode = starttime.networknode

                          and eventtime is not null

                           order by endtimetable.eventtime

               ) is null then daydiff(tolocal(starttime.eventtime),getdate()) < 30

           else(

                     select top 1 eventtime from events as endtimetable

                     where endtimetable.eventtime >starttime.eventtime

                     and endtimetable.eventtype = 5001

                     and endtimetable.message like 'bgppeerstate is%'

                     and endtimetable.networknode = starttime.networknode

                     and eventtime is not null

                   )  end as upeventtime,datetrunc('minute',starttime.eventtime,

                case when (select top 1 eventtime from events as endtime

                                    where endtimetable.eventtime > starttime.eventtime

                                      and endtimetable.eventtype = 5001

                                      and endtimetable.message like 'bgppeerstate is%'   

                                      and endtimetable.networknode = starttime.networknode

                                      and eventtime is not null

                                 )end) as outagedurationinminutes

                             from events starttime

                            left join nodes on starttime.networknode=nodes.nodeid

                            where starttime.eventtype = 5000

                            and datetrunc('minute',starttime.eventtime)=adddate('minute',-1,getdate())

                            and starttime.message like 'bgppeerstate is down%'

                                    and branch_voc = 'true'

      ) as uptimetable

      order by caption asc, downeventtime desc

        • Re: error with case statement
          mesverrum

          It's not your case i think, it's that SWQL doesn't accept a query that doesn't pull from one of the built in tables, you can't do things like roll your own CTE's and whatnot.  It also doesn't really do wildcard type columns, you'd need to individually specify the name of the columns you want it to return instead of the select top 100 from ()

          1 of 1 people found this helpful