1 2 Previous Next 15 Replies Latest reply: Jul 13, 2014 3:13 AM by salute-Salem RSS

    How could this code work ?

    salute-Salem

      hi ,

      i have this code in a form , and it works properly , i do not know how ? because i tried it after modifying it , removing the quotes and parenthesis , it does not work ,

      SET_BLOCK_PROPERTY('TTO_TICKETTRAVELORDERDETAILS',DEFAULT_WHERE,'NVL(STATUS,''$$##&&&&'') = NVL('''||:CONTROL.STATUS||''',NVL(STATUS,''$$##&&&&'')) 

                                                                      AND  IS_TOUR   = ''N''                                                               

                                                                      AND EXISTS (SELECT ''X''

                                                                                  FROM   TTO_ORDERDETAILSROUTES T

                                                                                  WHERE  T.ORDERDETAILID = TTO_TICKETTRAVELORDERDETAILS.ORDERDETAILID

                                                                                  AND    NVL(CARRIER,''$$##'') = NVL('''||:CONTROL.CARRIER||''',NVL(CARRIER,''$$##''))

                                                                                  AND    NVL(CLASS ,''$$&&'')  = NVL('''||:CONTROL.CLASS||''', NVL(CLASS ,''$$&&'')) )                                                                                                                               

                                                                      AND  EXISTS  ( SELECT ''X''

                                                                                     FROM   TTO_TICKETTRAVELORDERHEADER H

                                                                                     WHERE  H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                                                                     AND    STATUS = ''Post'' )

                                                                      AND     EXISTS  ( SELECT ''X''

                                                                                        FROM   TTO_TICKETTRAVELORDERHEADER H

                                                                                        WHERE  H.ORDERID                       = TTO_TICKETTRAVELORDERDETAILS.ORDERID                                                                                  

                                                                                        AND    TO_CHAR(ORDERDATE,''DD-MM-YYYY'') BETWEEN NVL('''||:CONTROL.FROM_DATE||''' ,ORDERDATE ) AND NVL('''||:CONTROL.TO_DATE||''' ,ORDERDATE )

                                                                                        AND    NVL(H.CUSTOMER_NO,''$$##@@@@'') = NVL('''||:CONTROL.CUSTOMER_NO||''',NVL(H.CUSTOMER_NO,''$$##@@@@'')) )');                            

      after modifying it , and trying it in the pl/sql developer , it could identify the TTO_TICKETTRAVELORDERDETAILS.ORDERID

      and TTO_TICKETTRAVELORDERDETAILS.ORDERDETAILID

      in the "Exists" conditions

       

      SELECT

        t.*,

        t.rowid

      FROM

        TTO_TICKETTRAVELORDERDETAILS t

      WHERE

        NVL(STATUS,'$$##&&&&') = NVL('',NVL(STATUS,'$$##&&&&'))

      AND IS_TOUR              = 'N'

      AND EXISTS

        (

          SELECT

            'X'

          FROM

            TTO_ORDERDETAILSROUTES T

          WHERE

            T.ORDERDETAILID       = TTO_TICKETTRAVELORDERDETAILS.ORDERID

          AND NVL(CARRIER,'$$##') = NVL('',NVL(CARRIER,'$$##'))

          AND NVL(CLASS ,'$$&&')  = NVL('', NVL(CLASS ,'$$&&'))

        )

      AND EXISTS

        (

          SELECT

            'X'

          FROM

            TTO_TICKETTRAVELORDERHEADER H

          WHERE

            H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

          AND STATUS  = 'Post'

        )

      AND EXISTS

        (

          SELECT

            'X'

          FROM

            TTO_TICKETTRAVELORDERHEADER H

          WHERE

            H.ORDERID = T.TTO_TICKETTRAVELORDERDETAILS.ORDERID

          AND ORDERDATE BETWEEN NVL('',ORDERDATE ) AND NVL('',ORDERDATE )

          AND NVL(H.CUSTOMER_NO,'$$##@@@@') = NVL('',NVL(H.CUSTOMER_NO,'$$##@@@@')));

       

       

       

      why ?

        • 1. Re: How could this code work ?
          Andreas Weiden

          What exactly is your question? Do you want us to count the quotes?

          • 2. Re: How could this code work ?
            salute-Salem

            HA HA HA HA HA HA HA HA ,

            Funny Andria ,

             

            i think the question is obvious :

            again

            why pl/sql developer does not see the columns in the subquery , such this one "TTO_TICKETTRAVELORDERDETAILS.ORDERID" ,

            and "Oracle Forms" does ?

            • 3. Re: How could this code work ?
              Andreas Weiden

              i think the question is obvious :

               

              Really? Thats what you wrote in your initial post:

               

              after modifying it , and trying it in the pl/sql developer , it could identify the TTO_TICKETTRAVELORDERDETAILS.ORDERID

              and TTO_TICKETTRAVELORDERDETAILS.ORDERDETAILID

              in the "Exists" conditions

               

              ..

              ..

               

              why?

              There's nothing in it like "does not see the columns". And about your (now clearer) question... We don't know your datamodel. Check if the referenced columns exist in the referenced tables, or if the necessary grants are made.

              • 4. Re: How could this code work ?
                salute-Salem

                Really? Thats what you wrote in your initial post:

                So it's obvious twice now ,

                Check if the referenced columns exist in the referenced tables, or if the necessary grants are made.

                the code works in "Oracle Forms" andrea , i can query after setting the "where clause" property ,

                then the columns and the tables and everything are existed .

                • 5. Re: How could this code work ?
                  Andreas Weiden

                  Well, then try to check the value of :SYSTEM.LAST_QUERY and compare it with what you issued in pl/sql developer. For me, the alias t in your outer query looks suspicious.

                  • 6. Re: How could this code work ?
                    salute-Salem

                    this is the :system.last_query value

                    SELECT ROWID,

                           ORDERDETAILID,

                           DEPARTUREDATE,

                           CLASSTYPE,

                           FROMCITY,

                           TOCITY,

                           CARRIER,

                           CLASS,

                           FLIGHTNO,

                           FLIGHTDATE

                      FROM TTO_ORDERDETAILSROUTES

                    WHERE (ORDERDETAILID = '299b6112-b88b-4cd0-8bd9-651090fbf0d1')

                    order by SEQUENCE ASC

                     

                    but i do not know how ? where is the "default where" value which was set in the "Pre-Query" trigger ,

                    and order by property is set to another column , but the query above show another order by column ? how ?

                     

                    and the above table is another table not the table in the first post ?

                    • 7. Re: How could this code work ?
                      Marwim

                      Ora-01400 wrote:

                       

                      Really? Thats what you wrote in your initial post:

                      So it's obvious twice now ,

                      Check if the referenced columns exist in the referenced tables, or if the necessary grants are made.

                      the code works in "Oracle Forms" andrea , i can query after setting the "where clause" property ,

                      then the columns and the tables and everything are existed .

                      To me the only obvious thing is that you got Andreas' name wrong twice.

                       

                      You should simplify your query, e.g. why do you write

                      NVL(H.CUSTOMER_NO,'$$##@@@@') = NVL('',NVL(H.CUSTOMER_NO,'$$##@@@@'))

                      It will aways be true, just like

                      AND ORDERDATE BETWEEN NVL('',ORDERDATE ) AND NVL('',ORDERDATE )

                       

                      When you have sorted this out, then it's easier to spot the error

                       

                      FROM  TTO_TICKETTRAVELORDERDETAILS t

                      ...

                      AND EXISTS (

                          SELECT  'X'

                          FROM    TTO_ORDERDETAILSROUTES T

                          WHERE   T.ORDERDETAILID  = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                       

                      You give TTO_TICKETTRAVELORDERDETAILS the alias t and in the subquery you reference it with the full table name and in the subquery you give TTO_ORDERDETAILSROUTES the same alias.

                      It should be something like

                      AND EXISTS (

                          SELECT  'X'

                          FROM    TTO_ORDERDETAILSROUTES OD

                          WHERE   OD.ORDERDETAILID  = T.ORDERID

                       

                       

                      Regards

                      Marcus

                       

                      BTW: TO_CHAR(ORDERDATE,''DD-MM-YYYY'') BETWEEN will compare strings instead of dates.

                      • 8. Re: How could this code work ?
                        salute-Salem

                        i found another block which executes it's query last ,

                        i removed it , and saw the query we talk about , it is :

                        SELECT ROWID,

                               ORDERDETAILID,

                               PAX_NAME,

                               ORDERID,

                               TICKETNO,

                               PNR,

                               AMOUNT,

                               AGETYPE,

                               TICKETAPPROVED,

                               TEMP_TICKETNO,

                               STATUS

                          FROM TTO_TICKETTRAVELORDERDETAILS

                        WHERE NVL(STATUS, '$$##&&&&') = NVL('', NVL(STATUS, '$$##&&&&'))

                           AND IS_TOUR = 'N'

                           AND EXISTS

                        (SELECT 'X'

                                  FROM TTO_ORDERDETAILSROUTES T

                                 WHERE T.ORDERDETAILID = TTO_TICKETTRAVELORDERDETAILS.ORDERDETAILID

                                   AND NVL(CARRIER, '$$##') = NVL('', NVL(CARRIER, '$$##'))

                                   AND NVL(CLASS, '$$&&') = NVL('', NVL(CLASS, '$$&&')))

                           AND EXISTS (SELECT 'X'

                                  FROM TTO_TICKETTRAVELORDERHEADER H

                                 WHERE H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                   AND STATUS = 'Post')

                           AND EXISTS (SELECT 'X'

                                  FROM TTO_TICKETTRAVELORDERHEADER H

                                 WHERE H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                   AND TO_CHAR(ORDERDATE, 'DD-MM-YYYY') BETWEEN

                                       NVL('', ORDERDATE) AND NVL('', ORDERDATE)

                                   AND NVL(H.CUSTOMER_NO, '$$##@@@@') =

                                       NVL('', NVL(H.CUSTOMER_NO, '$$##@@@@')))

                        order by ORDERDETAILID

                         

                        and i executed it , and it retrieved the same records as the form does , but i do not know what is the difference between it , and the above one in the first post ?

                        2-  in this subquery for example

                        AND EXISTS (SELECT 'X'

                                  FROM TTO_TICKETTRAVELORDERHEADER H

                                 WHERE H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                   AND STATUS = 'Post')

                        what does it mean with "TTO_TICKETTRAVELORDERDETAILS.ORDERID" column ? is it the column in the outer query ? is it a correlated subquery ? or what ?

                        • 9. Re: How could this code work ?
                          Andreas Weiden

                          Looks like the block where you're query is on has another detail-block whichh is queried automatically. Try the Blokc-property LAST_QUERY instead.

                          • 10. Re: How could this code work ?
                            salute-Salem

                            Try the Blokc-property LAST_QUERY instead.

                            there is not a property with such this name in the block properties ?

                            - i removed the entire block to try it out .

                             

                            - when i modified the very above code in the first post to this , it did not retrieve data , i do not know why

                            SELECT

                             

                            *

                             

                             

                             

                              FROM TTO_TICKETTRAVELORDERDETAILS

                             

                            WHERE

                             

                            NVL(STATUS, '$$##&&&&') = NVL('', NVL(STATUS, '$$##&&&&'))

                             

                            AND IS_TOUR = 'N'

                             

                            AND EXISTS

                             

                            (

                             

                              SELECT

                             

                               'X'

                             

                                FROM TTO_ORDERDETAILSROUTES T

                             

                               WHERE

                             

                               T.ORDERDETAILID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                             

                            AND NVL(CARRIER, '$$##') = NVL('', NVL(CARRIER, '$$##'))

                             

                            AND NVL(CLASS, '$$&&') = NVL('', NVL(CLASS, '$$&&'))

                             

                              )

                             

                            AND EXISTS

                             

                            (

                             

                              SELECT

                             

                               'X'

                             

                                FROM TTO_TICKETTRAVELORDERHEADER H

                             

                               WHERE

                             

                               H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                             

                            AND STATUS = 'Post'

                             

                              )

                             

                            AND EXISTS

                             

                            (

                             

                              SELECT

                             

                               'X'

                             

                                FROM TTO_TICKETTRAVELORDERHEADER j

                             

                               WHERE

                             

                               j.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                             

                            AND ORDERDATE BETWEEN NVL('', ORDERDATE) AND NVL('', ORDERDATE)

                             

                            AND NVL(j.CUSTOMER_NO, '$$##@@@@') = NVL('', NVL(j.CUSTOMER_NO, '$$##@@@@')));

                             

                            it worked but 0 row selected ? i think it is the same code of the :system.last_query ?

                            and please answer my previous questions about how did it read the column , and if it is correlated or not ?

                             

                            3- is there any way to read a specific query not the last ?

                             

                            thanks to all of you .

                            • 11. Re: How could this code work ?
                              Andreas Weiden

                              LAST_QUERY is a valid block-property, at leat is actual versions of forms (how to use last_query built in?)

                               

                              - when i modified the very above code in the first post to this , it did not retrieve data , i do not know why

                              Well, you will have to find out yourself, try removing one WHERE-condition after the other and see whcih one is causing the prblem

                              • 12. Re: How could this code work ?
                                Marwim

                                Hello,

                                 

                                the query is equivalent to

                                SELECT  *

                                FROM    TTO_TICKETTRAVELORDERDETAILS

                                WHERE   IS_TOUR = 'N'

                                AND EXISTS(

                                    SELECT  'X'

                                    FROM    TTO_ORDERDETAILSROUTES T

                                    WHERE   T.ORDERDETAILID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                    )

                                AND EXISTS(

                                    SELECT  'X'

                                    FROM    TTO_TICKETTRAVELORDERHEADER H

                                    WHERE   H.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                    AND     STATUS = 'Post'

                                    )

                                AND EXISTS(

                                    SELECT  'X'

                                    FROM    TTO_TICKETTRAVELORDERHEADER j

                                    WHERE   j.ORDERID = TTO_TICKETTRAVELORDERDETAILS.ORDERID

                                    );

                                The other predicates will always be TRUE in this case

                                So execute it and if you get no rows, then remove one EXISTS.

                                 

                                "TTO_TICKETTRAVELORDERDETAILS.ORDERID" references the column from the table in the outer query

                                • 13. Re: How could this code work ?
                                  salute-Salem

                                  SELECT ROWID,

                                         ORDERDETAILID    

                                              

                                    FROM TTO_TICKETTRAVELORDERDETAILS

                                  WHERE NVL(STATUS, '$$##&&&&') = NVL('', NVL(STATUS, '$$##&&&&'))

                                     AND IS_TOUR = 'N'

                                     AND EXISTS

                                  (SELECT 'X'

                                            FROM TTO_ORDERDETAILSROUTES T

                                           WHERE T.ORDERDETAILID = TTO_TICKETTRAVELORDERDETAILS.ORDERDETAILID                                              -- here

                                             AND NVL(CARRIER, '$$##') = NVL('', NVL(CARRIER, '$$##'))

                                             AND NVL(CLASS, '$$&&') = NVL('', NVL(CLASS, '$$&&')))

                                  In a correlated subquery , must not i put an alias to the outer table , and use it in the subquery like this " T.ORDERDETAILID" ,

                                  Or i can use it without doing so ?

                                   

                                  3- is there any way to read a specific query not the last ?

                                  • 14. Re: How could this code work ?
                                    Marwim

                                    If you don't give the table in the outer query an alias then you reference it with the full name.

                                     

                                    3. ??

                                    1 2 Previous Next