Getting No Data After Adding Parameters to My Data Model

Sherie Ross

My data model was returning many rows of data prior to adding parameters. I added the parameters to the data set created via the Query Builder tool. Here is what I started with:

And then I added this and it stopped resulting in any data:

And here are my data model properties:

Mallikarjuna Kuppauru-Oracle

Hi @Sherie Ross

Can you try with OR condition and see are you able to get the data?

Regards,

Arjun

Mallikarjuna Kuppauru-Oracle

Hi @Sherie Ross

My suggestion is to comment out one by one parameter and see which is causing the issue. I saw you are using AND condition for all parameters.

Regards,

Arjun

Sumanth V -Oracle

@Sherie Ross - Please validate your query to ensure that valid data exists that satisfies all the conditions. You can substitute the parameter values directly in the SQL and try validating it in the database to see if it yields results. If it doesn't, you may need to revisit the query.

Sherie Ross

Sumanth V -Oracle I validated and shortened some column names.

Mallikarjuna Kuppauru-Oracle I added the parameters one at a time and ended up with only the trans status and from date - to date working. You mentioned that I was using AND. Should I be using something else?

Here is what I have right now:

where "Transaction Details"."Process Name" NOT IN ('Add Contingent Worker', 'Add Nonworker', 'Add Pending Worker', 'Administer Salary', 'Cancel Work Relationship', 'Edit Grade', 'Edit Grade Rate', 'Edit Job', 'Edit Location', 'Edit Organization', 'Edit Position', 'Hire an Employee', 'Learning Assignment Completion', 'Learning Incident', 'Learning Request', 'Manage Areas of Responsibility', 'Manage Directs', 'Manage Employment', 'Termination')
AND DESCRIPTOR_IDOF("Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Transaction Status") <> 'ORA_HRC_TXN_COMPLETED'
AND "Transaction Details"."Transaction Status" IN (:P_Trans_Status)
/AND "Transaction Details"."Submitted by User Name" IN (:P_Submit_By)/
/AND "Transaction Details"."Process Name" IN (:P_Proc_Name)/
/AND "Transaction Details"."Approval Status" IN (:P_Apprv_Status)/
/AND "Transaction Details"."Approval State" IN (:P_Apprv_State)/
/AND "Issue Details"."Assigned to User" IN (:P_Assign_To)/
/AND "Issue Details"."Issue Status" IN (:P_Issue_Status)/
AND "Transaction Details"."Submitted Date" >= (:P_Date_From)
AND "Transaction Details"."Submitted Date" <= (:P_Date_To)

Sherie Ross

@Mallikarjuna Kuppauru-Oracle - Thanks so much for your suggestions. They were helpful. I used a combination of AND and OR. Here is what worked for me:

WHERE
(("Transaction Details"."Process Name" NOT IN ('Add Contingent Worker'
AND (DESCRIPTOR_IDOF("Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Transaction Status") <> 'ORA_HRC_TXN_COMPLETED'))
AND "Transaction Details"."Submitted Date" >= (:P_Date_From)
AND "Transaction Details"."Submitted Date" <= (:P_Date_To)
AND "Transaction Details"."Transaction Status" IN (:P_Trans_Status)
OR "Transaction Details"."Process Name" IN (:P_Proc_Name)
OR "Transaction Details"."Approval Status" IN (:P_Appr_Status)
OR "Transaction Details"."Approval Status" IN (:P_Appr_State)
OR "Issue Details"."Assigned to User Name" IN (:P_Assign_To)
OR "Issue Comment"."Comment by User Name" IN (:P_Comm_By)
OR "Transaction Details"."Submitted by User Name" IN (:P_Submit_By)