7 Replies Latest reply: Feb 23, 2016 4:44 AM by Naveen Chintala RSS

    Invalid Expression error

    Naveen Chintala

      Hi Experts,

       

      I have this code, and Im facing this invalid syntax error for the second expression. though both are same it is showing error. Can anyone help me please...

      Temp1:

      Load  Date as _kMasterCalendarDate,

           Triparty_bilateral_ind as subclassification,

           Amt_Lcl as AMT,

           bucket as bucket,

           Resid_Tnor as RT,

           product_group as product_group,

           Amt_Lcl*Resid_Tnor as WAMNumerator

           Resident SecFund_Full

           where (classification='Reverse Repo' And Triparty_bilateral_ind='Reverse Repo')

           OR (classification='Repo' And Triparty_bilateral_ind='Bilateral repo')

           OR (classification='Repo' And Triparty_bilateral_ind='Tri-party repo');

       

      Temp2:    

      Load  Date as _kMasterCalendarDate,

           Triparty_bilateral_ind as subclassification,

           sum(Amt_Lcl) as AMT,

           bucket as bucket,

           Resid_Tnor as RT,

           product_group as product_group,

           sum(Amt_Lcl*Resid_Tnor) as WAMNumerator

           Resident SecFund_Full

         where (classification='Reverse Repo' And Triparty_bilateral_ind='Reverse Repo')

           OR (classification='Repo' And Triparty_bilateral_ind='Bilateral repo')

           OR (classification='Repo' And Triparty_bilateral_ind='Tri-party repo');

       

      Temp1 is running succesfully but not temp2. the condition used for both is same but some modifications in field values.

        • Re: Invalid Expression error
          Tamil Nagaraj

          You should use Group by statement.

           

          Temp2:   

          Load  Date as _kMasterCalendarDate,

               Triparty_bilateral_ind as subclassification,

               sum(Amt_Lcl) as AMT,

               bucket as bucket,

               Resid_Tnor as RT,

               product_group as product_group,

               sum(Amt_Lcl*Resid_Tnor) as WAMNumerator

               Resident SecFund_Full

             where (classification='Reverse Repo' And Triparty_bilateral_ind='Reverse Repo')

               OR (classification='Repo' And Triparty_bilateral_ind='Bilateral repo')

               OR (classification='Repo' And Triparty_bilateral_ind='Tri-party repo')

          Group by Date,Triparty_bilateral_ind, bucket, Resid_Tnor, product_group ;

          • Re: Invalid Expression error
            Amit Saini

            Naveen,

             

            Not sure why you are doing below with resident load:

             

            sum(Amt_Lcl) as AMT, ---> This could be issue , try without this first !

             

            Thanks,

            AS

            • Re: Invalid Expression error
              Prashant Sangle

              Hi,

               

              whenever you use aggregation functions you have to use group by clause.

              and in you Temp2 load you are using aggregation functions without group clause therefore it is showing you error.

               

               

              for details see aggregation function  in  help menu

               

              Regards

              • Re: Invalid Expression error
                Thabang Petje

                Hi,

                 

                Remove sum(Amt_Lcl*Resid_Tnor) in the second load and have it the same as the first load (Amt_Lcl*Resid_Tnor)

                • Re: Invalid Expression error
                  Jonathan Dienst

                  First of all, you need a group by for all the non-aggregated values.

                  Second, second table will auto concatenate into the first, so you will land up double counting everything.

                   

                  Because of that, its not clear what you are trying to do - perhaps you should explain this in detail if you want accurate and useful assistance.

                  • Re: Invalid Expression error
                    Puttemans Johan

                    Hello Naveen,

                     

                    What exactly do you want to have as a result? As pointed out by others before, you cannot use the SUM expression the way you do in the second load, and that is why you get the error. So it is not the code under the 'resident' that poses the problem, but the code above.

                     

                    You could use Temp1 to calculate the sums, or you could use a mapping and then map the totals in the Temp1. It depends on your aim.

                     

                    Regards,

                     

                    Johan

                      • Re: Invalid Expression error
                        Naveen Chintala

                        I have a subclassification field and bucket field with values 1 week, 2week, 1month, 2months, >3months

                        I want to insert a new bucket value "Total" which has a value of amount for that particular classification or subclassification field.

                        and the requirement is asCapture.JPG

                        I can calculate this total by using an expression in pivot table but the thing is bucket if a field which is pivoted to be on top as shown in picture. and If I use an expression in chart, it gives total column for each of bucketvalue. but that is not required actually. the requirement is as above.