1 2 Previous Next 26 Replies Latest reply: Jun 19, 2013 11:00 PM by Mahir M. Quluzade Go to original post RSS
      • 15. Re: 2 Left outer join error
        yxes2013

        Hi MCA,

         

        I already created this view before with one left join only,  I just added a new left join for look up table countrymaster.

        There something wrong with the syntax . Can you give me some syntax of two left join please.

        • 16. Re: 2 Left outer join error
          yxes2013

          I run the query on the schema user onwer

           

          Hi Greg, can you teach me how to apply format on  you post? Thanks

          • 17. Re: 2 Left outer join error
            Greg Spall

            When entering a reply, at the top right of the box you're typing in, is the option "Use advanced editor".

            Click it.

             

            Now you're on a new screen, Select the text you want to format. Then on the top bar, to the right, is a double blue arrow (>>), click it,

            Choose Syntax Highlighting, then SQL.

             

            If you ran the query, please SHOW the results here so we can see.

            • 18. Re: 2 Left outer join error
              Mahir M. Quluzade

              Then use below script

               

              SELECT A.ACRNUMBER AS ACR_NO, A.LASTNAME, A.GIVENNAME AS FIRSTNAME, A.MIDDLENAME, A.BIRTHDATE, A.GENDER,NLV2(F.COUNTRYID3,'XXX'), A.PROBSTAYLENGTH AS PROB_STAY_LENGHT,A.SECTIONISSUED AS SECTION_ISSUED, A.RESIDENCECERTIFICATENUMBER AS RESIDENCE_CERTIFICATE_NO, A.ACTIVESTATUS AS STATUS, B.CLIP
              IMAGE AS PHOTO, C.SIGBMP AS SIGNATURE,D.CARDISSUErNUMBER AS CARD_NO, D.CARDEXPIRYDATE AS CARD_VALIDITY, D.CARDSERIALNUMBER AS CARD_SERIAL_NO, D.CARDISSUEDATE AS CARD_DATE_ISSUED, nvl2( d.CARDSERIALNUMBER,'0','1') AS CARD_STATUS
                     From   Acrmaster  A Inner Join  Pictures  B  On A.Acrnumber=B.Acrnumber 
                                     Inner Join  Signature  C On B.Acrnumber=C.Acrnumber
                                     Inner Join  Acrcarddetails D On   C.Acrnumber=D.Acrnumber
                                     Left Outer Join Acrblockcarddetails E On (D.Acrnumber=E.Acrnumber And D.Cardserialnumber=E.Cardserialnumber)
                                     Left Outer Join Countrymaster_Lk F ON  A.NATIONALITY=F.ID
              

               

               

              You  have mistake  there :

               

              left outer join ACRBLOCKCARDDETAILS E

                 Left Outer Join Countrymaster_Lk F on d.acrnumber=e.acrnumber and d.CARDSERIALNUMBER=e.CARDSERIALNUMBER and A.NATIONALITY=F.ID

               

               

               

              Regards

              Mahir M. Quluzade

              • 19. Re: 2 Left outer join error
                yxes2013

                Hi Greg,

                 

                I want the formatting similar to

                [code]

                [/code]

                In the old forum

                Thanks

                 

                What can you expect? if it is the schema owner?

                 

                SQL> select *

                  2    from user_tab_privs

                  3   where table_name in (

                  4                    'ACRMASTER', 'PICTURES', 'SIGNATURE',

                  5                    'ACRCARDDETAILS', 'ACRBLOCKCARDDETAILS', 'COUNTRYMASTER_LK'

                  6                   );

                 

                 

                no rows selected

                • 20. Re: 2 Left outer join error
                  Greg Spall

                  yxes2013 wrote:

                   

                  I want the formatting similar to

                  [code]

                  [/code]

                  In the old forum

                  Thanks

                   

                  Not going to happen, sorry.

                  Welcome to the *NEW* forums

                   

                  yxes2013 wrote:

                   

                  What can you expect? if it is the schema owner?

                   

                  SQL> select *

                    2    from user_tab_privs

                    3   where table_name in (

                    4                    'ACRMASTER', 'PICTURES', 'SIGNATURE',

                    5                    'ACRCARDDETAILS', 'ACRBLOCKCARDDETAILS', 'COUNTRYMASTER_LK'

                    6                   );

                   

                   

                  no rows selected

                  Well, this shows the problem. You are creating a Materialized view, or a "Compiled" object. Compiled objects do NOT use roles. So the above query should show the access to the tables. If it does not, then your View cannot see the tables it needs.

                   

                  You need to issue grants directly on this schema to select those tables:

                   

                  ie:
                  grant select on ACRMASTER to <yourschema>;
                  
                  • 21. Re: 2 Left outer join error
                    Ashu_Neo

                    Why are you mixing ANSI SQL with normal joins; which is creating confusion!

                    Not tested. But try with this changed portion in your query mentioned below.

                    ......
                    ......
                    FROM
                        acrmaster      a,
                        pictures       b,
                        signature      c,
                        acrcarddetails d ,
                        acrblockcarddetails e,
                        countrymaster_lk f
                        --left outer join acrblockcarddetails e
                    --on d.acrnumber=e.acrnumber and d.cardserialnumber=e.cardserialnumber
                        --left outer join countrymaster_lk f
                      --on
                      --d.acrnumber=e.acrnumber and d.cardserialnumber=e.cardserialnumber and a.nationality=f.id
                      where
                      a.acrnumber=b.acrnumber
                      and b.acrnumber=c.acrnumber
                      and c.acrnumber=d.acrnumber
                      and d.acrnumber=e.acrnumber(+) and d.cardserialnumber=e.cardserialnumber(+)
                      and a.nationality=f.id(+)
                      ;

                    • 22. Re: 2 Left outer join error
                      yxes2013

                      Thanks mahir,

                       

                      I got new error

                       

                      Oppps sorry my mistake....I run it on another schema. It is working now

                       

                      Thanksss

                      • 23. Re: 2 Left outer join error
                        yxes2013

                        Thanks Ashu,

                         

                        Yes I have tried that but most gurus preferred the new ANSI code? as this if more efficient?

                        • 24. Re: 2 Left outer join error
                          Ashu_Neo

                          I don't find, there any performance related stuff exists in comparison! OR may be! 

                          But yes, It has been preferred overtimes, because of good readability and flexibility towards other databases including oracle( standardized by ANSI). And it has some extra features too. Like "FULL OUTER JOINS".

                          Even you can omit "OUTER" word while writing (From 11g onwards).

                           

                          Thanks!

                          • 25. Re: 2 Left outer join error
                            yxes2013

                            I thank YOU ALL

                            • 26. Re: 2 Left outer join error
                              Mahir M. Quluzade

                              You are welcome!

                              1 2 Previous Next