Solution for SQL with two joined tables

Scriptcase 9 General
#1

I have grid with two tables table A and table B.key joined to A.name The resulting Grid should be sorted by tableB.fiieldC. In standard sql this is achieved by joining the two tables. In scriptcase (Version 9.3.6) this produces an error. (SELECT * FROM tableA JOIN tableB WHERE tableB.key =tableA.name)
Is there a solution?

#2

Hi sawjer,
you’re from Swiss Montains - great region!
Therefore let me explain in German:

Beim Join von zwei Tabellen und der Benutzung von Wildcards, also dem * im SELECT kann es zu Problemen kommen.
So k?nnen zum Beispiel zwei Feldnamen in den beiden Tabellen identisch sein (id ?). Damit ist die Zuordnung in Scriptcase nicht mehr eindeutig m?glich.

Daher empfehlen wir beim Coaching den Programmieren einfach den SQL Builder von Scriptcase zu verwenden, um den (LEFT) JOIN zu erstellen und zu testen. Dabei werden unter anderem solche ‘Eindeutigkeits-Probleme’ gleich umgangen.

Und ja. Ich verwende sehr h?ufig Joins in den SQL Befehlen f?r Grids - auch in der aktuellen Version. Hatte dabei bislang keine Probleme.

Aber vielleicht verstehe ich die Frage auch nicht ganz und es dreht sich um etwas anderes.

Auf jeden Fall w?nsche ich viel Erfolg und ein sch?nes Wochenende!

Viele Gr??e aus M?nchen.

Gunter Eibl

#3

Hallo GunterEibl
Thank you for the response.

To clarify my problem: I need to display a list of records in table A. The field tableA.name links to tableB.field1. Now of course, this is easily dealt with in an event “onLoad record”.
My specific problem is: I need to sort the list by table B.field2. This will only happen if the sorting is done in the SQL settings.
For this i use in the sql->setting the join ON (tableA.name=tableB.field1). The field names of tableA and tableB are distinct and in the select are written fully qualified.
Scriptcase reports an error: “Grid fields undefined”. This tells me, that scriptcase does not know the fields of the joined table.

I run scriptcase 9.3,0.6

So here I am stuck! Help is very welcome

sawjer from the snow mountains

#4

Hi Sawjer, you need to specify the field list in your select statement. Scriptcase does not expand the " * ".

#5

Thank you tfertil. Yes, I understand, I have writen all the fields fully qualified. For this I receive the Error “Grid fields undefined”.
I am glad for more help.

#6

Hi sawjer,
here is a example using two tables out of Scriptcases sample project:

SELECT
   customers.customerid,
   customers.companyname,
   customers.contactname,
   customers.contacttitle,
   orders.orderid,
   orders.customerid,
   orders.employeeid,
   orders.orderdate,
   orders.requireddate,
   orders.shippeddate
FROM
   customers LEFT OUTER JOIN orders ON customers.customerid = orders.customerid

You can generate this with Scriptcases SQLEditor in minutes and just paste it while a new grid.
The syntax generated this way (and tested in SQLEditor) works perfect almost 100% times.

If you’ll need further assistance on this, please contact me via PM.

Sincerely

Gunter Eibl

#7

Thank you GunterEibl, it works!
But only:

  • if the specific syntax of sql builder is used
  • when creating the application, this sql has to be entered into the field “SQL select statement” AND the table field has to be left empty

Great Help. Better Docu by the part of scriptcase would help!