Re: SQL error
Tim
Aside from the missing colon - which had been corrected - I also believe
the statement is valid, since it's working in another method in the DB. To
eliminate copy and past errors, I retyped the SQL block, but still got the
error.
What I didn't do is quit and restart 4D - after that the code ran as
expected.
Go figure.
On Wed, Nov 1, 2017 at 12:02 PM, Timothy Penner via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> > I think you have multiple issues with this SQL Statement.
>
> I was wrong. I didn’t fully understand the SQL statement at the time of my
> last reply, upon further inspection it seems the SQL is valid (apart from
> the missing : in the variable reference).
>
> There must be something else wrong, like using this statement inside of a
> SQL function {fn} call for example. I think we are missing some context
> about how you are using this method, because executing this code in a
> method by itself is working in a sample database here.
>
> -Tim
>
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ: http://lists.4d.com/faqnug.html
> Archive: http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
--
David Witton
**
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: SQL error
Milan Thanks - after I posted, I caught that and corrected, but still get the same error. On Wed, Nov 1, 2017 at 10:06 AM, Milan Adamov via 4D_Tech < 4d_tech@lists.4d.com> wrote: > No “:” in front of $arrCount? > > Milan > > Sent from my iPad > > > On Nov 1, 2017, at 17:04, David Witton via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > > > Here's a SQL block that throws Error code: 1427, Nested Begin/End SQL not > > allowed. > > > > Begin SQL > > SELECT Athena.First_Name, Athena.Last_Name, Athena.DOB, Count(*) as > recCount > > from Athena > > Group by Athena.First_Name,Athena.Last_Name,Athena.DOB > > HAVING Count (*)>1 > > into :$arrFld1,:$arrFld2,:$arrFld3, $arrCount; > > End SQL > > > > > ** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** -- David Witton ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
SQL error
Here's a SQL block that throws Error code: 1427, Nested Begin/End SQL not allowed. Begin SQL SELECT Athena.First_Name, Athena.Last_Name, Athena.DOB, Count(*) as recCount from Athena Group by Athena.First_Name,Athena.Last_Name,Athena.DOB HAVING Count (*)>1 into :$arrFld1,:$arrFld2,:$arrFld3, $arrCount; End SQL First, I copied it directly from another method in the same DB that references another table. I just changed the table and field names- that method works correctly. Next, I retyped the block from scratch - still the same result. Any ideas on what to try next are welcome. -- David Witton ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
identify duplicates
I'm looking for a strategy to identify duplicate records in a table - that is, records for which 3 fields are identical across two or more records - or in another case, where a single field is not unique. Does anyone have a suggestion on how to proceed? -- David Witton ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
