1 2 Previous Next 15 Replies Latest reply on Dec 7, 2015 7:18 AM by okramis

    ExecuteSQL Problem

    mr.voyager

      Hi everyone,

       

      I'm trying to adapt the Outer Join Demo of the Kevin Frank.

      Only one diffrence than the demo is that I have 70 Columns.

       

      Problem is in the $$theArray variable. It's getting blank results (except first six columns).

      But, if I write the SELECET clauses with 30 repetition in the eSQL, there is no problem. Everything works fine.

      When i expand the column with SELECT clauses its working so weird like this.

      I'm pulling my hair out - What's my wrong?

       

      Thanks in advance for any help,

       

      Recep

       

       

      PS: Here is my $$theArray variable script;

       

      Let ( [

      x1 = GetValue ( $$YemekIDList ; 1 );

      x2 = GetValue ( $$YemekIDList ; 2 );

      x3 = GetValue ( $$YemekIDList ; 3 );

      x4 = GetValue ( $$YemekIDList ; 4 );

      x5 = GetValue ( $$YemekIDList ; 5 );

      x6 = GetValue ( $$YemekIDList ; 6 );

      x7 = GetValue ( $$YemekIDList ; 7 );

      x8 = GetValue ( $$YemekIDList ; 8 );

      x9 = GetValue ( $$YemekIDList ; 9 );

      x10 = GetValue ( $$YemekIDList ; 10 );

      x11 = GetValue ( $$YemekIDList ; 11 );

      x12 = GetValue ( $$YemekIDList ; 12 );

      x13 = GetValue ( $$YemekIDList ; 13 );

      x14 = GetValue ( $$YemekIDList ; 14 );

      x15 = GetValue ( $$YemekIDList ; 15 );

      x16 = GetValue ( $$YemekIDList ; 16 );

      x17 = GetValue ( $$YemekIDList ; 17 );

      x18 = GetValue ( $$YemekIDList ; 18 );

      x19 = GetValue ( $$YemekIDList ; 19 );

      x20 = GetValue ( $$YemekIDList ; 20 );

      x21 = GetValue ( $$YemekIDList ; 21 );

      x22 = GetValue ( $$YemekIDList ; 22 );

      x23 = GetValue ( $$YemekIDList ; 23 );

      x24 = GetValue ( $$YemekIDList ; 24 );

      x25 = GetValue ( $$YemekIDList ; 25 );

      x26 = GetValue ( $$YemekIDList ; 26 );

      x27 = GetValue ( $$YemekIDList ; 27 );

      x28 = GetValue ( $$YemekIDList ; 28 );

      x29 = GetValue ( $$YemekIDList ; 29 );

      x30 = GetValue ( $$YemekIDList ; 30 );

      x31 = GetValue ( $$YemekIDList ; 31 );

      x32 = GetValue ( $$YemekIDList ; 32 );

      x33 = GetValue ( $$YemekIDList ; 33 );

      x34 = GetValue ( $$YemekIDList ; 34 )

       

      ];

       

      ExecuteSQL ( "SELECT a.\"__id\" ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

      ( SELECT Porsiyon FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" )

       

      FROM MST AS a"

       

      ; "•" ; "" ;

       

      x1 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x2 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x3 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x4 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x5 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x6 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x7 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x8 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x9 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x10 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x11 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x12 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x13 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x14 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x15 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x16 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x17 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x18 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x19 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x20 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x21 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x22 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x23 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x24 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x25 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x26 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x27 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x28 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x29 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x30 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x31 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x32 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x33 ; UPL::PlanTarihi ; UPL::_idOgun ;

      x34 ; UPL::PlanTarihi ; UPL::_idOgun

       

      )

       

      )

        • 1. Re: ExecuteSQL Problem
          okramis

          On which FM-version are you, does your $$YemekIDList return valid results for every value?

          And does your subquery return a single value, you might need to set: SELECT DISTINCT Porsion....

          • 2. Re: ExecuteSQL Problem
            mr.voyager

            Hello okramis, I'm using FM Pro Adv v14.0.4 on Mac OS El Capitan.

            Yes, $$YemekIDList is returning valid results for every value. I'm checking everytime...

             

            Your mentioned for the subquery is like that;

             

            query = "SELECT a.\"__id\" ,

            ( SELECT DISTINCT ( Porsiyon ) FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

            ...

            • 3. Re: ExecuteSQL Problem
              okramis

              mr.voyager schrieb:

               

              Hello okramis, I'm using FM Pro Adv v14.0.4 on Mac OS El Capitan.

              Yes, $$YemekIDList is returning valid results for every value. I'm checking everytime...

               

              Your mentioned for the subquery is like that;

               

              query = "SELECT a.\"__id\" ,

              ( SELECT DISTINCT ( Porsiyon ) FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

              ...

              there are no brackets needed: ( SELECT DISTINCT Porsiyon FROM vl_UPLITEMS..... subqueries must return a single value, so check your subquery if this is the case. Normally you would have something like SELECT COUNT(*) or SUM(Porsiyon).... in the subquery, this would grant the single value.

              • 4. Re: ExecuteSQL Problem
                mr.voyager

                it doen't work with;

                 

                query = "SELECT a.\"__id\" ,

                ( SELECT DISTINCT ( Porsiyon ) FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

                 

                i guess your mentioned for the $$YemekIDList and its already DISTINCT...

                • 5. Re: ExecuteSQL Problem
                  mr.voyager

                  and doesn't work without brakets.

                  I'll be trying with SUM

                  • 6. Re: ExecuteSQL Problem
                    mr.voyager

                    Same result with SUM;

                     

                    SELECT SUM ( Porsiyon ) FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"__id\" ) ,

                    ...

                    • 7. Re: ExecuteSQL Problem
                      mr.voyager

                      by the way, i'm fetching column titles to another variable with same method without problem in everyone of 70 columns.

                      but i can't do this

                      • 8. Re: ExecuteSQL Problem
                        mr.voyager

                        and an another same result with;

                         

                        query = "SELECT DISTINCT a.\"_idMUS\" ,

                        ( SELECT SUM ( Porsiyon ) FROM vl_UPLITEMS AS b WHERE \"_idYMK\" = ? AND PlanTarihi = ? AND \"_idOgun\" = ? AND \"_idMUS\" = a.\"_idMUS\" ) ,

                        ...

                         

                        i can't go more further than 30th.

                        • 9. Re: ExecuteSQL Problem
                          okramis

                          Can you post your file here? I did some tests and couldn't find any limits yet, so I assume there's a culprit somewhere in the data.

                          • 10. Re: ExecuteSQL Problem
                            beverly

                            HOW do you get your 'array', $$YemekIDList?

                             

                            The main difference I see from the article is that Kevin uses global FIELD with this notation:

                                 table::field[x]

                            and you use the $$variable with this notation:

                                 GetValue($$variable; x)

                             

                            Are you sure all your "values" have values? the List() function, for example. will give you only non-blank values in a list.

                             

                            I rarely use $$variables, so cannot address any possible issue with limitations, though you seem to only have a list in your 'array'.

                             

                            beverly

                            • 11. Re: ExecuteSQL Problem
                              mr.voyager

                              Here is my file.

                              admin pw is 1234

                              • 12. Re: ExecuteSQL Problem
                                mr.voyager

                                Hello Beverly,

                                 

                                I'm getting the $$YemekIDList with eSQL in the same script.

                                I can't see any blank value when i look to the $$YemekIDList in DataViewer.

                                 

                                Let([

                                query = "SELECT DISTINCT \"_idYMK\" FROM vl_UPLITEMS WHERE PlanTarihi = ? AND \"_idOgun\" = ?";

                                result = ExecuteSQL( query ; "" ; "" ; UPL::PlanTarihi; UPL::_idOgun)

                                ];

                                result

                                )

                                • 13. Re: ExecuteSQL Problem
                                  okramis

                                  Hi mr.voyager, after a long investigation, it seems the limitation is in this expression:

                                   

                                  AND \"_idMUS\" = a.\"__id\"

                                   

                                  if I substitute it with AND \"_idMUS\" = 17 (that's where you have 34 records for) then the query works.

                                  I did another try with an events table of my own and it behaves the same: max 30 subqueries who have a constraint with a field of the main query, so you'd have to split your report into 3, if you need 70 columns.

                                   

                                  Tomorrow I'll have a test with FM13 and MSSQL to see if this is common behavior.

                                   

                                  Best

                                  Otmar

                                  • 14. Re: ExecuteSQL Problem
                                    mr.voyager

                                    Many many thank you Otmar. You saved a lot of time for me. You are great.

                                    i've splited the $$theArray to 2 for the momement and its works.

                                    Tomorrow i'll be continue to 3.

                                     

                                    PS: i've been tested before with FM13 and results are same as with FM14.

                                     

                                    Best regards and have nice day.

                                     

                                    Recep Güney

                                    1 2 Previous Next