AnsweredAssumed Answered

Can this be done - ExecuteSQL Question

Question asked by jamiebah on Apr 7, 2018
Latest reply on Apr 22, 2018 by fmpdude

Let me start by saying this, I am only trying to find out if its possible to do this from a single ExecuteSQL statement

I know this can be done many different ways including not using ExecuteSQL but that is not the point of my question.

 

 

Here is the question:

Is it possible to select distinct values from a field, then using the results of the "select distinct", join data from other fields in the same table and "append" them to the result? (forgive me if my terminology is incorrect)

 

The concept of the statement would be something like this:

SELECT <field(s)> FROM <table(s)> WHERE <some field>='Yes'

 

What I would like to see:

cust0000001,inv0000001,inv0000002

cust0000002,inv0000003,inv0000006

cust0000003,inv0000004,inv0000005

 

 

So, can this be done using some combination of JOIN, CONCAT, nested selects, etc? (and/or any other SQL commands/functions I may be forgetting about)

 

 

Every ExecuteSQL statement attempt I have tried (including using group by, order by, etc) always returns results similar to this:

cust0000001,inv0000001

cust0000001,inv0000002

cust0000002,inv0000003

cust0000002,inv0000006

cust0000003,inv0000004

cust0000003,inv0000005

 

If this question has been answered before, I apologize but I was not able to find the answer...

 

 

Some things I have tried:

ExecuteSQL ( "SELECT \"_kflt__customerID\",\"_kplt__recordID\" from invoices2Payments_all WHERE zz_selectedForPayment = 'Yes' GROUP BY \"_kflt__customerID\",\"_kplt__recordID\" ORDER BY \"_kflt__customerID\"" ; "" ; "" )

ExecuteSQL (

  "SELECT b.\"_kflt__customerID\", b.\"_kplt__recordID\" FROM invoices2Payments_all b WHERE \"_kflt__customerID\" IN ( SELECT DISTINCT (a.\"_kflt__customerID\") FROM invoices2Payments_all a WHERE a.zz_selectedForPayment ='Yes' ) AND b.zz_selectedForPayment = 'Yes'

  GROUP BY b.\"_kflt__customerID\", b.\"_kplt__recordID\""

  ; "" ; ""

)

 

Both of the above have been tried with variations of using GROUP BY and ORDER BY, also without using them...

Outcomes