cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

error with case statement

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

0 Kudos
1 Reply

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 ()

- Marc Netterfield, Github