ExecuteSQL - newbie

[Mr.Voyager]

Hello,

I'm trying to create a virtual list ( 70 Columns X 300 Rows ) with the below codes.

But it fails after the 30th SELECT clause. I cannot figure out, please help.

Thanks,

Recep

 

 

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 );

query = "SELECT DISTINCT MusteriIsmi ,  

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

FROM vl_UPLItems AS a ORDER BY a.MusteriIsmi"; 

result = ExecuteSQL ( query ; "•" ; "" ; 

UPL::PlanTarihi ; UPL::_idOgun ; @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 
) ];

result

)   //   end

 

[beverly]

maybe this would help?

http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/

[Mr.Voyager]

Thank you Beverly but i was trying to adapt this ( http://filemakerhacks.com/2013/04/17/outer-joins-in-filemaker-12-part-3/ ) article to my solution.

its working till the 29th SELECT clause without problem.

Is there a limitation for Queries or Variable data?

Edit: these are my $$theArray data with before and after 30th SELECT clause.

before.tiff

after.tiff

Edited December 5, 2015 by Mr.Voyager
attachments