Announcement

Collapse
No announcement yet.

Mysql NESTED Join

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

  • Mysql NESTED Join

    Tried to make a NESTED Join within the SQL of a GRID APPLICATION. The query works fine in the SQL Builder in scriptcase and with SqlPro (desktop application). Here is the query.

    Code:
    SELECT a.iniid,
       a.cuiid,
       a.cuiid,
       c.wosLetter,
       a.insDescription,
       a.sriid,
       a.indDateOfOrder,
       a.indDateOrderDue,
       a.inbRushOrder
    FROM invoice a LEFT OUTER JOIN (workorder c INNER JOIN workflow d ON c.woiid = d.woiid)
    ON a.iniid = c.iniid
    WHERE d.wfiManagement = '2' OR d.wfiManagement = '1' AND a.loiid = 2
    Compile and Run application:
    Fatal error: Call to a member function MoveNext() on a non-object in /usr/local/zend/apache2/htdocs/scriptcase6/devel/lib/php/database.inc.php on line 882

    Sometimes lets me compile the application but when it loads and runs:
    Error while accessing the database:
    Column 'iniid' in field list is ambiguous
    SelectLimit(SELECT iniid, cuiid, cuiid as cuiid_1, insDescription, sriid, indDateOfOrder, indDateOrderDue, inbRushOrder from invoice a LEFT OUTER JOIN (workorder c INNER JOIN workflow d ON c.woiid = d.woiid) ON a.iniid = c.iniid where (d.wfiManagement = '2') OR (d.wfiManagement = '1') AND (a.loiid = 2), 22, 0)

    Thoughts as to why it will work in the SQL builder (scriptcase tool) and within my SQLPRO application. I would believe this to be in error within SCRIPTCASE not being able to use a NESTED SQL.
    Kris

  • #2
    Turn on debug mode in the application. Often the sql query you write becomes nested inside another query that scriptcase creates. Turning on debug mode allows you to see the full query and debug it. I've used nested queries and they work well in grids.

    Comment


    • #3
      Yes I too have had trouble like this and it has to do with things SC generates. These functions are made for single table use and I useually create views to achieve things like this.
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #4
        Hello,

        As rperrett said, turn on debug mode and use alias if necessary.

        regards,
        Bernhard Bernsmann

        Comment


        • #5
          +1 for views. I've had to implement them extensively.

          Comment


          • #6
            I created a view using SQL Query Builder in SC and get similar Error!

            ---------------------------------------------------------------------
            SELECT
            Diseases.DiseaseID,
            Diseases.ComonName,
            Diseases.ComonSubname,
            Diseases.MedicalName,
            Diseases.LatinName,
            Diseases.OtherName,
            `Recipe Component`.DiseaseID,
            `Recipe Component`.RecepieID,
            `Recipe Component`.ComponentCode,
            `Recipe Component`.ComponentDescription,
            `Recipe Component`.Unit,
            `Recipe Component`.Amount,
            `Recipe Ingredients`.DiseaseID,
            `Recipe Ingredients`.RecepieID,
            `Recipe Ingredients`.ProductId,
            `Recipe Ingredients`.IngredientName,
            `Recipe Ingredients`.IngredientDescription,
            `Recipe Ingredients`.Unit,
            `Recipe Ingredients`.Amount
            FROM
            dDISEASES Diseases INNER JOIN dRECCOMPONENTS Recipe Component ON Diseases.DiseaseID = `Recipe Component`.DiseaseID
            INNER JOIN dRECINGREDIENTS Recipe Ingredients ON Diseases.DiseaseID = `Recipe Ingredients`.DiseaseID
            ---------------------------------------------------------------------





            ERROR:

            Fatal error: Call to a member function FieldCount() on a non-object in C:\Program Files\NetMake\v6\wwwroot\scriptcase\devel\lib\php\ database.inc.php on line 855


            so how AM I supposed to implement a VIEW ?

            Arthur
            -----------------------------------------
            Arthur Klisiewicz
            dATA POINT SOFTWARE
            www.datapointsoftware.com

            Comment


            • #7
              Look at the comma after Diseases.OtherName ...
              Last edited by RHS; 10-18-2013, 03:33 PM.
              Best regards: - Reinhard -

              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

              Comment


              • #8
                thanks but,..it is not a comma....

                with or without the comma I get the same error.
                It seems like SC just expects certain number of fields in a QUEUE and SQL query deals with much more. Perhaps I'm worng but I still see nor resolution.
                I have seen some other users ask similar questions with using more advanced queries and I wonder if I placed the query in the right place (perhaps views could be only used in source code) ???

                BTW: this query was build with SC query builder
                SC6_SQLError.jpg

                Arthur
                -----------------------------------------
                Arthur Klisiewicz
                dATA POINT SOFTWARE
                www.datapointsoftware.com

                Comment


                • #9
                  Can you try this query?

                  Code:
                  SELECT
                  Diseases.DiseaseID,
                  Diseases.ComonName,
                  Diseases.ComonSubname,
                  Diseases.MedicalName,
                  Diseases.LatinName,
                  Diseases.OtherName,
                  RC.DiseaseID,
                  RC.RecepieID,
                  RC.ComponentCode,
                  RC.ComponentDescription,
                  RC.Unit,
                  RC.Amount,
                  RI.DiseaseID,
                  RI.RecepieID,
                  RI.ProductId,
                  RI.IngredientName,
                  RI.IngredientDescription,
                  RI.Unit,
                  RI.Amount
                  FROM dDISEASES AS Diseases 
                  INNER JOIN dRECCOMPONENTS AS RC ON Diseases.DiseaseID = RC.DiseaseID
                  INNER JOIN dRECINGREDIENTS AS RI ON Diseases.DiseaseID = RI.DiseaseID
                  
                  Best regards: - Reinhard -

                  I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                  Comment


                  • #10
                    Reinhard - thank you for your time...
                    this query looks little strange to me but it doesn't generate an error now. Although when I run it and open the grid it shows no records, so probably something is wrong with joints.

                    can you please explain how come you used "RI' instead "dRECINGREDIENT" as a real table name ?

                    Arthur
                    -----------------------------------------
                    Arthur Klisiewicz
                    dATA POINT SOFTWARE
                    www.datapointsoftware.com

                    Comment


                    • #11
                      INNER JOIN dRECINGREDIENTS AS RI => you can use RI.<fieldname>

                      Ok, what say the debug output (SQL String) when you start the app?

                      Best regards: - Reinhard -

                      I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                      Comment


                      • #12
                        in sc tool inner join not working

                        SELECT * from exp_familylevelintervention AS ab INNER JOIN family AS fc ON ab.fli_FmlId=fc.fml_Id order by fc.fml_Firstname

                        after run it show error "Search fields undefined"

                        please reply

                        Comment

                        Working...
                        X