4 Replies Latest reply: Jun 13, 2012 4:55 AM by jagan mohan rao appala RSS

    Syntax

      Whats wrong with my syntax? When I create the first table on its own, it runs fine, but when I try and add the if statement it fails. The debugger is not helpful.

       

       

       

      InterfaceTrafficDetailTemp:

      LOAD InterfaceID,

      NodeID,

      In_Averagebps as In_Averagebps1,

      (In_Averagebps/1000) AS In_Averagebps,

      (Out_Averagebps/1000) AS Out_Averagebps,

      ApplyMap('Map_InterfacesIn',NodeID&'-'&InterfaceID) AS InBandwidth,

      ApplyMap('Map_InterfacesOut',NodeID&'-'&InterfaceID) AS OutBandwidth,

      applymap('Map_InterfaceName',InterfaceID) AS InterfaceName,

      applymap('Map_BandwidthandCircuit',NodeID) AS BandwidthandCircuit

      FROM InterfaceTrafficDetail2.qvd(qvd);

       

       

      InterfaceTraffic1:

      Load

      *, IF(sum(InBandwidth)+sum(OutBandwidth)=0,0,

      IF(sum(InBandwidth)=0,sum(OutBandwidth/OutBandwidth) * 100,

      IF(sum(OutBandwidth),(sum(In_Averagebps)/sum(InBandwidth)) * 100,

      sum(Out_Averagebps/OutBandwidth)+sum(In_Averagebps/InBandwidth)*50))) AS AVERAGE_of_CircuitUtil_AvgRecvXmit2

      RESIDENT InterfaceTrafficDetailTemp;

       

      DROP Table InterfaceTrafficDetailTemp1;

        • Syntax
          m w

          Sum must be used with Group By

          • Syntax
            jagan mohan rao appala

            Hi,

             

            You need to use Group By, if you use Aggregate functions sum, max etc. 

             

            Regards,

            Jagan.

              • Syntax

                Okay, so that worked but took forever to complete. How can I speed the function up?

                  • Re: Syntax
                    jagan mohan rao appala

                    Hi Alex,

                     

                    Try this, this may faster a bit because we are calculating values once and reusing in If.

                     

                    InterfaceTraffic1:

                    Load

                         *,

                    IF((InBandWidthTotal +OutBandwidthTotal) =0,0,

                    IF(InBandWidthTotal=0,sum(OutBandwidth/OutBandwidth) * 100,

                    IF(OutBandwidthTotal, (In_AveragebpsTotal/InBandWidthTotal) * 100,

                    sum(Out_Averagebps/OutBandwidth)+sum(In_Averagebps/InBandwidth)*50))) AS AVERAGE_of_CircuitUtil_AvgRecvXmit2;

                    LOAD

                         *,

                         sum(InBandwidth) AS InBandWidthTotal,

                         sum(OutBandwidth) AS OutBandwidthTotal,

                         sum(In_Averagebps) AS In_AveragebpsTotal

                    RESIDENT InterfaceTrafficDetailTemp

                    Group by *;

                     

                    Regards,

                    Jagan.