9 Replies Latest reply: Dec 10, 2012 12:06 AM by 972794 RSS

    Creating ViewObject from Entity object

    972794
      HI,
      Is it possible to modify the query of the view object? And I have created that view object from EntityObject.


      Regards,
      Infy
        • 1. Re: Creating ViewObject from Entity object
          Arunkumar Ramamoorthy-Oracle
          Yes. http://docs.oracle.com/cd/E21043_01/web.1111/b31974/bcquerying.htm
          • 2. Re: Creating ViewObject from Entity object
            972794
            Hi,
            That is really worth reading. But my issue is not yet solved. My query is little complicated, it joins 8 difference queries. Here are the steps I followed.

            1) Created Associations between the entities.
            2) Created VO, and selected appropriate association.


            When I run the application, data is not getting displayed, though I have enough data. What is going wrong?

            Regards,
            Infy
            • 3. Re: Creating ViewObject from Entity object
              Arunkumar Ramamoorthy-Oracle
              Infy,

              Always mention your JDev version.

              Can you elaborate on what you mean by " My query is little complicated, it joins 8 difference queries"? You mean you have a query that has union of 8 different queries? Or you mean there are different joins for the query's where clause?

              Try incremented approach of adding association one by one, so that you would know where the issue is.

              Also, try debugging the output to console (Where to put "jbo.debugoutput=console" in WLS? and see if the proper query is being executed.

              -Arun
              • 4. Re: Creating ViewObject from Entity object
                972794
                Hi Arun,
                Thanks for your reply. My query has union of 8 different queries. I have created Association between them. But while creating view object, I am getting struck in choosing the association & join type. Can I use expert mode? View object is getting displayed. But when I try to insert any rows, it is throwing errors. My JDev version is, 11.1.2.2.0.. Here is the query:

                <SQLQuery><![CDATA[
                Select * from (Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.Dph_Id Placeholder_Id,
                Decode(b.Format,Null,c.default_value,To_Char(To_Date(c.Default_Value,'DD-MON-YYYY HH24:MI:SS'),b.format)) Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                0 text_dir,
                'N' is_obsolete,
                'NA' Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b,
                Data_Placeholders c
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.Dph_Id=c.Placeholder_Id
                And b.Dph_Id Is Not Null
                Union
                Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.Vph_Id Placeholder_Id,
                c.Default_Value Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                0 text_dir,
                'N' is_obsolete,
                'NA' Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b,
                Visit_Ph c
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b.Version_No
                And a.Label_Id=c.Label_Id
                And a.Version_No=c.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.Vph_Id=c.Placeholder_Id
                And b.Vph_Id Is Not Null
                Union
                Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.rtph_id Placeholder_Id,
                c.Default_Value Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                0 text_dir,
                'N' is_obsolete,
                'NA' Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b,
                rtph c
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b.Version_No
                And a.Label_Id=c.Label_Id
                And a.Version_No=c.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.rtph_id=c.Placeholder_Id
                And b.rtph_id Is Not Null
                Union
                Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.ftph_id Placeholder_Id,
                c.placeholder_data Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                d.text_direction text_dir,
                'N' is_obsolete,
                'NA' Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b,
                freetext_ph c,
                languages d
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b
                .Version_No
                And a.Label_Id=c.Label_Id
                And a.Version_No=c.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.ftph_id=c.Placeholder_Id
                And b.ftph_id Is Not Null
                And b.LANG_CD = d.LANG_CD
                Union
                Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.tph_id Placeholder_Id,
                c.description Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                d.text_direction text_dir,
                nvl(c.IS_OBSOLETE,'N') is_obsolete,
                'NA' Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b,
                phrase_translation c,
                languages d
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.tph_id=c.Placeholder_Id
                And b.Tph_Placeholder_Seq=c.placeholder_seq
                And b.Lang_Cd=c.Lang_Cd
                And b.tph_id Is Not Null
                And b.LANG_CD = d.LANG_CD
                Union
                Select a.Appr_Comment Remarks,
                a.placeholder_seq placeholder_Seq,
                a.Comment_Seq comment_Seq,
                b.Cph_Id Placeholder_Id,
                LABDB07.Cph_Value(b.Label_Id,b.Version_No,b.Cph_Id) Value,
                b.label_id label_Id,
                b.version_no version_No,
                a.user_id user_Id,
                0 text_dir,
                'N' is_obsolete,
                LABDB07.Cph_Format(b.Label_Id,b.Version_No,b.Cph_Id) Cph_Format,
                b.tph_placeholder_seq Ph_Version
                From Approver_Comments a,
                Label_Placeholders b
                Where a.Label_Id=b.Label_Id
                And a.Version_No=b.Version_No
                And a.Placeholder_Seq=b.Placeholder_Seq
                And b.Cph_Id Is Not Null) order by comment_Seq
                ]]></SQLQuery>
                • 5. Re: Creating ViewObject from Entity object
                  972794
                  Hi Arun,
                  Did you get any idea about my issue? Is there any possibilities to create view object for this query?


                  Regards,
                  Infy

                  Edited by: Infanta on Dec 6, 2012 9:47 PM
                  • 6. Re: Creating ViewObject from Entity object
                    Alejandro Tovar Lanz
                    Hi Infanta,

                    Have you try to run that query on your standalone sql worksheet? Remember you have to be careful with the join of the tables. If you are using inner joins then if just one table is missing information you wont get any results...
                    • 7. Re: Creating ViewObject from Entity object
                      972794
                      Hi,
                      Thanks for your reply. The issue is solved. I tried it in Expert mode.


                      Regards,
                      Infy
                      • 8. Re: Creating ViewObject from Entity object
                        bigchill
                        Hi

                        You ask if you can use Expert mode but the query you pasted is already in expert mode. As I know it, the query when placed in expert mode puts it in the CDATA section and when not in expert mode puts it outside of cdata.
                        So I doubt that just by changing it to expert mode did the trick, or have you done something else? and how come it was in CDATA and not in expert mode. I thought they were not mutually exclusive.
                        • 9. Re: Creating ViewObject from Entity object
                          972794
                          Hi,
                          That query I got it from some existing file. I pasted the query alone without including cdata. I just changed it to Expert mode, and I changed the Binding option from Oracle Named to Oracle positional. It worked.