Syntax

[#1]

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;

[m_woolf]

Sum must be used with Group By

[jagan · Accepted solution]

Hi,

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

Regards,

Jagan.

[#4]

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

[jagan]

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.