A Simple Query | Learn with Rego

Document created by navzjoshi00 Champion on Mar 3, 2017Last modified by navzjoshi00 Champion on Mar 3, 2017
Version 4Show Document
  • View in full screen mode

Dear Community Members,

 

Do you need some extra queries in your tool box or help getting Jaspersoft to recognize your HTML? 

We took your questions in today's Q&A.

 

1. Benefit Plan - Simple Query

 

2. Crosstab Cell Style (Reading Text vs HTML)

 

3. Unable to Delete Location

 

Please feel free to comment on any alternative answers you've found. At Rego, we always love your input. 

 

  

1.

Anyone have a simple query to pull the fields for the benefit plan of record on the project?

Answer

 

                FROM    inv_investments i
                        JOIN odf_ca_project ocp ON i.id = ocp.id
                        JOIN fin_plans fp ON fp.objectid = i.id
                                                             AND fp.is_plan_of_record = 1
                                                             AND fp.plan_type_code = 'FORECAST'
                        JOIN fin_plans bp ON bp.id = fp.benefit_plan_id

In my case, for my "from,"  I just needed the description field for the benefit plan.

 

 

 

2.

I have a report where I've used a SQL Aggregate function to concatenate Team Resources (roles included). I have concatenated HTML tags, so only the Roles show highlighted.

The part where Jasper is not working as I expected it to is the display. It considers the whole field as text, instead of considering the HTML text as HTML.

I set the markup to HTML and tried the same thing with the Styled option too.

Has anyone successfully tried this before?

Answer

 

If you're using the - RTRIM (XMLAGG  - function to aggregate, it will convert the field into text, so the tags are considered text too.
Try replacing it with ListAgg. Works like a charm. (Note: we have tags in the SQL rather than Jasper Variable + Style.)

 

 

3. 

I am not able to delete a location. The error is as follows:


The location is tied to a department, but when I try to delete the association, I get the following error:

 

Here's the error in the log file:

 

 

I think this is the bottleneck, but I haven't found a solution.

1.    None of the projects are using the location
2.    None of the resources are using the location
3.    The location is not used in any of the financial transactions or financial plans


Answer

Here's a query that can help drill down to the usage:

SELECT TC.TABLE_NAME, TC.COLUMN_NAME
, 'SELECT ''' || TC.TABLE_NAME || ''' TABLE_NAME, ''' || TC.COLUMN_NAME || ''' COLUMN_NAME, COUNT(*) CNT FROM ' || TC.TABLE_NAME || ' WHERE ' || TC.COLUMN_NAME || ' = ''myLocationID'' UNION ALL' vSQL
FROM USER_TAB_COLUMNS TC
WHERE 1=1
AND (TC.COLUMN_NAME LIKE '%LOCN%' OR TC.COLUMN_NAME LIKE '%LOCATION%')
AND TC.TABLE_NAME NOT LIKE '%_V' AND TC.TABLE_NAME NOT LIKE '%_V2'
AND TC.TABLE_NAME NOT LIKE 'NBI_%' AND TC.TABLE_NAME NOT LIKE 'TEMP_%' AND TC.TABLE_NAME NOT LIKE 'Z_%'
ORDER BY TC.TABLE_NAME, TC.COLUMN_NAME

 

The query looks in the data dictionary tables for fields that could reference a location. To look for fields that could reference the location, replace the “myLocationID” with the ID of the location you’re trying to delete. You can execute the queries in the vSQL column to see if any records actually reference your location.
In the case of the user above, we found the location in Finance > Setup > Defaults.

 

Thank You------------------------------  
 

Attachments

    Outcomes