Announcement

Collapse
No announcement yet.

Solution for SQL with two joined tables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Solution for SQL with two joined tables

    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?
    From the Swiss Mountains

  • #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
    Gunter Eibl
    email@GunterEibl.de

    asdw.de - Scriptcase & Open Source projects (German/English)
    scriptcase.coach - professional coaching for Scriptcase users (German)
    GunterEibl.de - About me (German)

    Comment


    • #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

      From the Swiss Mountains

      Comment


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

        Comment


        • #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.
          From the Swiss Mountains

          Comment


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

            Code:
            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
            Gunter Eibl
            email@GunterEibl.de

            asdw.de - Scriptcase & Open Source projects (German/English)
            scriptcase.coach - professional coaching for Scriptcase users (German)
            GunterEibl.de - About me (German)

            Comment


            • #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!
              From the Swiss Mountains

              Comment

              Working...
              X