6 Replies Latest reply: Mar 10, 2016 7:30 AM by JIng Zhou RSS

    Nested Aggregation not allowed in text object expression

      Hi Community

       

      I have this sql below:

       

      from region r, lineinfo_o l

      where r.region_id = l.region_id

      and l.snapshotid = 0

      and l.requestmessage_fk is not null

      and exists(select 1 from messages where messageid = l.requestmessage_fk and type in('PDO', 'UDO') and direction = 'REQUEST');

       

      And I am trying to write a script in a text object expression which does the same thing. Below is my solution:

       

      ='\nPDO Paths Sent to NBN (excluding Full SO):' &sum(if(SNAPSHOTID=0 AND if(IsNull(REQUESTMESSAGE_FK), 1, 0) AND if(sum(if(MESSAGEID=REQUESTMESSAGE_FK AND match(TYPE, 'PDO', 'UDO') AND DIRECTION='REQUEST'))>=1, 1, 0), 1, 0))

       

      But QV gives me an 'Nested aggregation not allowed error'. So my questions are

      1. How to achieve my goal without having this error?

      2. Is there a better convention to write script in expression (as squeezing everything in one line is messy and not readable)?

       

      Thanks

      Jing

        • Re: Nested Aggregation not allowed in text object expression
          Sunny Talwar

          Can you put in words what you are doing? Syntax is not right and difficult to understand what you trying to do with this expression?

            • Re: Nested Aggregation not allowed in text object expression

              Sorry, I just noticed one line of my sql script is missing. It should be:

               

              select count(*)

              from region r, lineinfo_o l

              where r.region_id = l.region_id

              and l.snapshotid = 0

              and l.requestmessage_fk is not null

              and exists(select 1 from messages where messageid = l.requestmessage_fk and type in('PDO', 'UDO') and direction = 'REQUEST');

               

              So basically I wanna the QV script does the same thing as the sql script above by using sum, if, isnull and match functions.

              • Re: Nested Aggregation not allowed in text object expression

                And the tables are loaded from a database. The script is shown below:

                 

                SET ThousandSep=',';

                SET DecimalSep='.';

                SET MoneyThousandSep=',';

                SET MoneyDecimalSep='.';

                SET MoneyFormat='$#,##0.00;-$#,##0.00';

                SET TimeFormat='h:mm:ss TT';

                SET DateFormat='D/MM/YYYY';

                SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';

                SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                SET FirstWeekDay=0;

                SET BrokenWeeks=1;

                SET ReferenceDay=0;

                SET FirstMonthOfYear=1;

                SET CollationLocale='en-AU';

                 

                 

                ODBC CONNECT TO QlikView (XUserId is JALdASdNOTcSGbVMSbYGHWRNaG, XPassword is GPMPASdNOTcSGbVMSbYGHWRNVF);

                SQL SELECT REGIONID as REGION_ID

                FROM DONRIVERCSLL.REGION;

                 

                 

                SQL SELECT *

                FROM DONRIVERCSLL.LINEINFO;

                 

                 

                SQL SELECT DRLINEINFOID_FK as DRLINEINFOID,

                  OVERRIDEIGNOREFLAG

                FROM OVERRIDELINEINFO;

                 

                 

                SQL SELECT *

                FROM DONRIVERCSLL.MESSAGES;

                  • Re: Nested Aggregation not allowed in text object expression
                    Onno van Knotsenburg

                    Forget about script for a few minutes.

                     

                    Could you please try to write in human words (not SQL) what you want to achieve?

                      • Re: Nested Aggregation not allowed in text object expression

                        So imaging there are 3 tables (region, lineinfo and messages):

                         

                        region table          lineinfo table                                                                     message table

                        region_id              region_id     snapshot     requestmessage_id                   messageid     type     direction

                        1                           1                  0                 c                                                 b                    PDO     REQUEST

                        2                           2                  1                 a                                                 c                    UDO     REQUEST

                        3                           3                  0                 b                                                 a                    NDO     REQUEST

                        4                           4                  0                 null                                             d                    NDO      NO


                        The goal is when a region_id is selected in list box, I wanna a stastics count displayed on my text object. For example, if region_id=1 is selected then the count should be 1 as lineinfo has only one matching row and

                        its requestmessage_fk is not null and the inner sql (select count(*) from messages where messageid = l.requestmessage_fk and type in('PDO', 'UDO') and direction = 'REQUEST')query returns 1.

                        I hope this makes more sense.

                         

                        Thanks

                        Jing

                          • Re: Nested Aggregation not allowed in text object expression

                            region table          message table

                            region_id              messageid     type     direction

                            1                           b                    PDO     REQUEST

                            2                           c                    UDO     REQUEST  

                            3                           a                    NDO     REQUEST  

                            4                           d                    NDO     NO