This discussion is archived
9 Replies Latest reply: Dec 9, 2012 10:06 PM by 972794 RSS

Creating ViewObject from Entity object

972794 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Yes. http://docs.oracle.com/cd/E21043_01/web.1111/b31974/bcquerying.htm
  • 2. Re: Creating ViewObject from Entity object
    972794 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points