7 Replies Latest reply: Jul 16, 2013 5:46 AM by Varun Prakash Paulraj RSS

    IF and SUM in script load from SQL

    Claus Beck

      Hi

       

      I have made somecalculated fields in some charts, which I would like to use throughout my document, so I would like to add them add them to my script.

       

      Based on the two fields "SALESPRICE" and "SALESQTY" I was able to calculate and add the field "Sales Amount" to my script, but when I try to add "Total Discount" and "Net Sales Amount" (Which are shown as disabled below) my script fails - I get the error message "invalid expression"

       

      Is it not possible to use the IF and SUM function the way I try or have I just made a rookie mistake :-)

       

      Appreciate your help

       

      /Claus

       

       

       

       

      LOAD

       

       

       

       

       

      CUSTACCOUNT,
      SALESID,
      SALESPRICE,
      SALESQTY,
      SALESPRICE * SALESQTY as [Sales Amount],
      LINEDISC,
      LINEPERCENT,
      LINENUM,
      SALESSTATUS,
      // if(LINEPERCENT > 0, SUM((LINEPERCENT/100) * (SALESQTY * SALESPRICE)), SUM(LINEDISC)) as [Total Discount],
      // (SUM(SALESQTY * SALESPRICE) - if(LINEPERCENT > 0, SUM((LINEPERCENT/100) * (SALESQTY * SALESPRICE)), SUM(LINEDISC))) as [Net Sales Amount],
      ITEMID,

       

       

       

       

       

       

       

       

       

       

       

       

      SQL

       

      SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
      FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

       

       

       

         

       

       

        • Re: IF and SUM in script load from SQL
          Gysbert Wassenaar

          The problem is not sum and if in one expression. The problem is that if you use an aggregation function like sum, then all fields not used in aggregation functions need to be listed in a group by clause. But you can't use a group by with a preceding load. So you need to do the summing in the sql statement or use a resident load.

           

          There's also a comma after ITEMID instead of a semicolon.

           

          Edit: as Henric pointed out below I'm wrong about using a group by with a preceding load. It is possible. That doesn't mean it's a good idea though.

          • Re: IF and SUM in script load from SQL

            When you use the function SUM(), you need to put in your script the sintaxe GROUP_BY.

             

            Try use the IF statment inside the function SUM()

             

            SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC )  )

             

             

            Ex.:

             

            LOAD

              CUSTACCOUNT,

              SALESID,

              SALESPRICE,

              SALESQTY,

              SALESPRICE * SALESQTY as [Sales Amount],

              LINEDISC,

              LINEPERCENT,

              LINENUM,

              SALESSTATUS,

              ITEMID,

              SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Total Discount],

              SUM(SALESQTY * SALESPRICE) - SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Net Sales Amount]

            SQL

              SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID

              FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

             

            GROUP BY

              CUSTACCOUNT,

              SALESID,

              SALESPRICE,

              SALESQTY,

              SALESPRICE * SALESQTY as [Sales Amount],

              LINEDISC,

              LINEPERCENT,

              LINENUM,

              SALESSTATUS,

              ITEMID;

              • Re: IF and SUM in script load from SQL
                Claus Beck

                Thank you both of you for your help - It's much appreciated.

                 

                I've tried putting the If function into a sum and added the Group By function. It is however like the Group By function doesn't work

                 

                I get the error "OLEDB read failed" (which I also got before) when I'm trying the following:

                LOAD CUSTACCOUNT,
                SALESID,
                SALESPRICE,
                SALESQTY,
                SALESPRICE * SALESQTY as [Sales Amount],
                LINEDISC,
                LINEPERCENT,
                LINENUM,
                SALESSTATUS,
                SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Total Discount],
                    SUM(SALESQTY * SALESPRICE) - SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Net Sales Amount],
                    ITEMID;
                 
                SQL SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
                FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

                 

                GROUP BY
                  CUSTACCOUNT,
                  SALESID,
                  SALESPRICE,
                  SALESQTY,
                  SALESPRICE * SALESQTY as [Sales Amount],
                  LINEDISC,
                  LINEPERCENT,
                  LINENUM,
                  SALESSTATUS,
                  ITEMID;

                 

                 

                 

                 

                 

                 

                 

                Considering what @Gysbert wrote is it then possible to use the Group By function when it's a preceding load?

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

              • Re: IF and SUM in script load from SQL
                bobbyraj santhiogu

                Hi,

                Try this:


                TABLE1:
                SQL
                SELECT
                CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
                FROM
                "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
                ;


                TABLE2:
                LOAD
                *,
                SALESPRICE * SALESQTY as [Sales Amount],
                sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
                SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
                RESIDENT
                TABLE1
                GROUP BY
                CUSTACCOUNT,
                SALESID,
                SALESPRICE,
                SALESQTY,
                LINEDISC,
                LINEPERCENT,
                LINENUM,
                SALESSTATUS,
                ITEMID
                ;


                DROP TABLE TABLE1;

                 

                 

                or even this should work:

                 

                 

                SQL
                SELECT
                CUSTACCOUNT,
                SALESID,
                SALESPRICE,
                SALESQTY,
                LINEDISC,
                LINEPERCENT,
                LINENUM,
                SALESSTATUS,
                ITEMID,
                SALESPRICE * SALESQTY as [Sales Amount],
                sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
                SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
                FROM
                "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
                GROUP BY
                CUSTACCOUNT,
                SALESID,
                SALESPRICE,
                SALESQTY,
                LINEDISC,
                LINEPERCENT,
                LINENUM,
                SALESSTATUS,
                ITEMID
                ;


                • Re: IF and SUM in script load from SQL
                  Varun Prakash Paulraj

                  Very useful post

                   

                   

                   

                   

                   

                  Regards