General OBIEE

Get Involved. Join the Conversation.

Topic

    Ruzwan Akram
    Help with my SQL CodeAnswered
    Topic posted March 13, 2017 by Ruzwan AkramGold Medal: 3,500+ Points, tagged BI Publisher, BI Publisher Data Models, Core HR, SQL 
    135 Views, 1 Comment
    Title:
    Help with my SQL Code
    Summary:
    Help with my SQL Code
    Content:

    I am trying to create a BIP Report to show Document Payslips pdf uploaded for each employee (with HDL) which covers the Tables like  HR_DOCUMENTS_OF_RECORD, FND_DOCUMENTS_TL, ,FND_ATTACHED_DOCUMENTS and per_all_people_f with related to person_id.

    Problem is my BIP report with below query keeps erroring. The SQL is valid but the SQL query pulls back too many records. How can I get this to work ?

    select   "PER_ALL_PEOPLE_F"."PERSON_ID" as "PERSON_ID",

                "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER",

                "PER_PERSON_NAMES_F"."PERSON_ID" as "PERSON_ID1",

                "PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",

                "PER_PERSON_NAMES_F"."FIRST_NAME" as "FIRST_NAME",

                "HR_DOCUMENTS_OF_RECORD"."DOCUMENTS_OF_RECORD_ID" as "DOCUMENTS_OF_RECORD_ID",

                "HR_DOCUMENTS_OF_RECORD"."DOCUMENT_CODE" as "DOCUMENT_CODE",

                "HR_DOCUMENTS_OF_RECORD"."PERSON_ID" as "PERSON_ID_1",

                "HR_DOCUMENTS_OF_RECORD"."DOCUMENT_NAME" as "DOCUMENT_NAME",

                "FND_DOCUMENTS_TL_3"."DOCUMENT_ID" as "DOCUMENT_ID",

                "FND_DOCUMENTS_TL_3"."DESCRIPTION" as "DESCRIPTION",

                "FND_DOCUMENTS_TL_3"."TITLE" as "TITLE",

                "FND_DOCUMENTS_TL_3"."URL" as "URL",

                "FND_DOCUMENTS_TL_3"."DM_NODE" as "DM_NODE",

                "FND_DOCUMENTS_TL_3"."FILE_NAME" as "FILE_NAME",

                "FND_DOCUMENTS_TL_3"."DM_TYPE" as "DM_TYPE",

                "FND_DOCUMENTS_TL_3"."STATUS" as "STATUS",

                "FND_ATTACHED_DOCUMENTS"."DOCUMENT_ID" as "DOCUMENT_ID_1",

                "FND_ATTACHED_DOCUMENTS"."ENTITY_NAME" as "ENTITY_NAME",

                "FND_ATTACHED_DOCUMENTS"."PK1_VALUE" as "PK1_VALUE",

                "FND_ATTACHED_DOCUMENTS"."CATEGORY_NAME" as "CATEGORY_NAME"

     from   "FUSION"."FND_ATTACHED_DOCUMENTS" "FND_ATTACHED_DOCUMENTS",

                "FUSION"."FND_DOCUMENTS_TL" "FND_DOCUMENTS_TL_3",

                "FUSION"."FND_DOCUMENTS_TL" "FND_DOCUMENTS_TL_2",

                "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL_1",

                "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL",

                "FUSION"."HR_DOCUMENTS_OF_RECORD" "HR_DOCUMENTS_OF_RECORD",

                "FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",

                "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F"

     where              "PER_PERSON_NAMES_F"."PERSON_ID" =per_all_people_f.person_id

       and   "HR_DOCUMENTS_OF_RECORD"."PERSON_ID" =per_all_people_f.person_id

       and   "FND_ATTACHED_DOCUMENTS"."DOCUMENT_ID" =fnd_documents_tl_3.document_id

       and   "FND_ATTACHED_DOCUMENTS"."ENTITY_NAME" ='HR_DOCUMENTS_OF_RECORD'

       and   "FND_ATTACHED_DOCUMENTS"."PK1_VALUE" =hr_documents_of_record.DOCUMENTS_OF_RECORD_ID

    Best Comment

    Ruzwan Akram

    Issue resolved - I had the table  "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL" too many times in the From section of the SQL :)

    Comment

     

    • Ruzwan Akram

      Issue resolved - I had the table  "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL" too many times in the From section of the SQL :)