Analytical SQL: Rank Vs Dense Rank

Rank Vs Dense Rank

select e.*, rank() over( order by salary desc) from employees e;
-- rank returned as 1,2,3,3,5

select e.*, dense_rank() over( order by salary desc) from employees e;
-- rank returned as 1,2,3,3,4

RTF Template: Column Width

Issue:

When creating a BI Publisher rdf template in Microsoft Word, with intended output to Microsoft Excel; is there a way to specify that a table should use auto width for the columns in the Excel output.

By default, when you use the BI Publisher Desktop addin for Microsoft Word; the table wizard creates a table in Word that does not result in output to Excel where the columns in Excel is sized to fit the data. Users of the report then have to manually size the excel columns every time they run the report.

I did try "Autofit to Contents" in Microsoft Word, but it didn't work.

Resolution:

In order to fix this i had to follow these steps (Using MS Word 2010/RTF file)

1. Select any Cell on the Table in your RTF
2. MS Word-Ribbon-menu: Select Layout -> Select -> Select Table (this will highlight the table)
3. Right click the highlighted table and goto "Table Properties"
4. In Table Properties window goto Column tab and enter "Preferred width" some value.

Remarks: Total Preferred width can be > 100%

There is a value of preferred width 100% = ex: 16 cm 

then for each column, you may decide, how much width you need. and just place the value.

Your template may look little wierd, but output will be fine.

$FLEX$ Syntax

$FLEX$ Syntax

Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name         Manufacturer 
Value Set Name       Car_Maker_Name_Value_Set 
Validation Table     CAR_MAKERS 
Value Column         MANUFACTURER_NAME 
Description Column       MANUFACTURER_DESCRIPTION 
Hidden ID Column         MANUFACTURER_ID 
SQL Where Clause     (none) 

Segment Name         Model 
Value Set Name       Car_Model_Name_Value_Set 
Validation Table     CAR_MODELS 
Value Column         MODEL_NAME 
Description Column       MODEL_DESCRIPTION 
Hidden ID Column         MODEL_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set 

Segment Name         Color 
Value Set Name       Car_Color_Name_Value_Set 
Validation Table     CAR_COLORS 
Value Column         COLOR_NAME 
Description Column       COLOR_DESCRIPTION 
Hidden ID Column         COLOR_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set
                     AND MODEL_ID = 
                     :$FLEX$.Car_Model_Name_Value_Set 

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example. 

How To check if Text Contains Arabic Text in SQL

Use the below code snippet to check

case
  when ascii(substr(ipa.BENEFICIARY_NAME,-1,1)) between 55424 and 56255
  then 'ARABIC'
  else 'OTHER'
end LANG_FLAG

select ascii('؀') first_letter, ascii('ۿ') last_letter from dual;

FIRST ASCII VALUE: 55424, LAST ASCII VALUE 56255 

https://www.unicode.org/charts/PDF/U0600.pdf

http://jrgraphix.net/r/Unicode/0600-06FF

SQL Hints: /*+ MONITOR */

https://oracle-base.com/articles/11g/report_sql_monitor_text.txt

SQL HINT

/*+ MONITOR */

/*+ MONITOR */

The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.

SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM   emp e
       JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;


If you have long running statements you don't want to monitor, use the NO_MONITOR hint to prevent them being monitored.



-- 11gR2
SET LINESIZE 200
COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text
FROM   v$sql_monitor
WHERE  username = 'SCOTT';

SQL_ID        STATUS              SQL_TEXT
------------- ------------------- --------------------------------------------------------------------------------
526mvccm5nfy4 DONE (ALL ROWS)     SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
                                  FROM   emp e
                                         JOIN dept d ON e.deptno = d.deptno
                                  GROUP BY d.dname
                                  ORDER BY d.dname
                                 


Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.
*/

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '526mvccm5nfy4',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF                                 



SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SCOTT (53:1261)
 SQL ID              :  526mvccm5nfy4
 SQL Execution ID    :  16777217
 Execution Started   :  03/19/2011 12:55:13
 First Refresh Time  :  03/19/2011 12:55:13
 Last Refresh Time   :  03/19/2011 12:55:13
 Duration            :  .014344s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@oel5-11gR2-demo.localdomain (TNS V1-V3)
 Fetch Calls         :  4

Global Stats
======================================================================
| Elapsed |   Cpu   |    IO    | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls |  Gets  |
======================================================================
|    0.01 |    0.01 |     0.00 |    0.00 |     0.00 |     4 |     41 |
======================================================================

SQL Plan Monitoring Details (Plan Hash Value=2970111170)
================================================================================================================================================
| Id |            Operation            |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                 |         | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
================================================================================================================================================
|  0 | SELECT STATEMENT                |         |         |      |         1 |     +0 |     1 |        3 |       |          |                 |
|  1 |   SORT GROUP BY                 |         |       4 |    7 |         1 |     +0 |     1 |        3 |  2048 |          |                 |
|  2 |    MERGE JOIN                   |         |      14 |    6 |         1 |     +0 |     1 |       14 |       |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID | DEPT    |       4 |    2 |         1 |     +0 |     1 |        4 |       |          |                 |
|  4 |      INDEX FULL SCAN            | PK_DEPT |       4 |    1 |         1 |     +0 |     1 |        4 |       |          |                 |
|  5 |     SORT JOIN                   |         |      14 |    4 |         1 |     +0 |     4 |       14 |  2048 |          |                 |
|  6 |      TABLE ACCESS FULL          | EMP     |      14 |    3 |         1 |     +0 |     1 |       14 |       |          |                 |
================================================================================================================================================

Using Boolean in Functions

Using Boolean in Functions

CREATE or replace FUNCTION testfn (
    date1 IN DATE
) RETURN BOOLEAN
    AS
BEGIN
    IF
        date1 > SYSDATE
    THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;

----
SET SERVEROUTPUT ON;
----
DECLARE
    v_2   VARCHAR2(10);
BEGIN
       
    v_2 := case when testfn('1-Jan-2019') then 'true' else 'false' end;
    DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(testfn('1-Dec-2019')));

    dbms_output.put_line(v_2);
    IF
        testfn('1-Jan-2019')
    THEN
        dbms_output.put_line('TRUE');
    ELSE
        dbms_output.put_line('FALSE');
    END IF;

END;



--convert boolean to int
SYS.DIUTIL.BOOL_TO_INT() function:

DECLARE
status BOOLEAN:= false;
BEGIN
  DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(status));
END;

This will return 1 for true and 0 for false (and null for null).

Designing XSL Sub Templates

Why?

1. Allow Reusable advance functionality for RTF templates.

1. Transform Data structure for section of report
2. create a stylesheet to manage a complex layout

What?

Its a xsl file, which consists of

• one or more <xsl:template> definitions, each containing
• block of formatting
• block of processing commands

How to Use?

Once its developed as subtemplate, its called from main rtf file for processing and formatting requirements.

High Level Development Process

1. xsl template contains serveral <xsl: template> definitions, these definitions contains rules to apply, when a specified node is matched.

2. main template import the xsl template, and also command to apply

3. upload the main template to report definition, and subtemplate to catalog.