MODEL - SQL clause introduced in 10g

[Sanja]

Hello!

MODEL clause seems to be not supported by Beautifier (v.7.0.1.1066).

For example see following query:

SELECT "Earning Record ID",
       rv1 "Rate Record 1",
       rv2 "Rate Record 2"
  FROM (SELECT err.EARNING_RECORD_ID "Earning Record ID",
               'Factor Subcategory = '   || LPAD(err.FACTOR_SUBCATEGORY_NAME, 28, ' ') ||
               '; Rule Tag = '           || LPAD(err.RULE_NAME, 4, ' ') ||
               '; Action = '             || LPAD(err.FACTOR_ACTION_NAME, 15, ' ') ||
               '; Measurement Factor = ' || LPAD(err.MEASUREMENT_UNIT_NAME, 7, ' ') ||
               '; Value = '              || err.VALUE "Rate Record"
          FROM QA_ER_RULE_V err WHERE err.EARNING_RECORD_ID IN (1,10,11))
 MODEL
   RETURN UPDATED ROWS
   PARTITION BY ("Earning Record ID")
   DIMENSION BY(ROW_NUMBER() OVER (PARTITION BY "Earning Record ID" ORDER BY "Rate Record") rnk)
   MEASURES("Rate Record" rt,
            CAST('' AS VARCHAR2(1000)) rv1,
            CAST('' AS VARCHAR2(1000)) rv2)
   RULES (rv1[0] = rt[1],
          rv2[0] = rt[2])

PL/SQL Beautifier formatted as:

SELECT "Earning Record ID", rv1 "Rate Record 1", rv2 "Rate Record 2"
  FROM (SELECT err.EARNING_RECORD_ID "Earning Record ID",
                'Factor Subcategory = ' ||
                 LPAD(err.FACTOR_SUBCATEGORY_NAME, 28, ' ') || '; Rule Tag = ' ||
                 LPAD(err.RULE_NAME, 4, ' ') || '; Action = ' ||
                 LPAD(err.FACTOR_ACTION_NAME, 15, ' ') ||
                 '; Measurement Factor = ' ||
                 LPAD(err.MEASUREMENT_UNIT_NAME, 7, ' ') || '; Value = ' ||
                 err.VALUE "Rate Record"
           FROM QA_ER_RULE_V err
          WHERE err.EARNING_RECORD_ID IN (1, 10, 11)) MODEL RETURN UPDATED ROWS PARTITION BY("Earning Record ID") DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY "Earning Record ID" ORDER BY "Rate Record") rnk) MEASURES("Rate Record" rt, CAST('' AS VARCHAR2(1000)) rv1, CAST('' AS VARCHAR2(1000)) rv2) RULES(rv1 0 = rt 1, rv2 0 = rt 2)

Respectively, '[' and ']' signs are removed from original query, and formatted query will not compile. Additionally, it will be fine to see support of analytical functions in future Beautifier versions.

Thanks.

 

[Marco Kalter]

It is indeed not yet supported. I have added this to the list of enhancement requests.