Home » SQL & PL/SQL » SQL & PL/SQL » Generate SQL via SQL
Generate SQL via SQL [message #683815] Tue, 23 February 2021 01:29 Go to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Hello all,

I am faced with a little problem Razz

Is it possible to convert via SQL the following string into a SQL command:
({7@72} * 1) + ({7@73} * -1)
This string should result in a SELECT according to the following pattern:
SELECT (CASE WHEN dim_id = 72 THEN val ELSE 0 END) * 1 + (CASE WHEN dim_id = 73 THEN val ELSE 0 END) * -1
FROM tab_data
WHERE dim_id IN (72, 73);

{7@72} defines that the 7 refers to the seventh column in tab_data, which is dim_id. I could certainly map this via CASE because there are eight different columns, but they always depend on the number before the @ symbol. The 72 after the @ is always the corresponding ID.

These column-ID-combinations can then be calculated with each other using basic arithmetic operations (in the example addition and multiplication). In addition, there can be more than the two column-ID-combinations like A + B / C.

Is this possible via SQL or do I have to use PL/SQL for this?

Thanks a lot

Michael
Re: Generate SQL via SQL [message #683816 is a reply to message #683815] Tue, 23 February 2021 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

[Updated on: Tue, 23 February 2021 02:00]

Report message to a moderator

Re: Generate SQL via SQL [message #683822 is a reply to message #683816] Tue, 23 February 2021 03:31 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Hi Michel,

sorry for the missing formats Smile
The Oracle version is Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

I do not currently have a test case because I am first looking for the answer as to whether it is at all possible to solve my problem via SQL or whether I have to use PL/SQL for this.

Best regards

Michael
Re: Generate SQL via SQL [message #683825 is a reply to message #683822] Tue, 23 February 2021 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A test case is something you can write, there is no more need than CREATE TABLE and INSERT statements.

For instance, you say: "the 7 refers to the seventh column in tab_data" so post a CREATE TABLE for this table.
Or, you have string like "({7@72} * 1) + ({7@73} * -1)", where are these strings? In a table? So post CREATE TABLE and INSERT statements for some possible data (formulas).

Then we can work with this test case to try to achieve your goal and show you our tries.
Otherwise, the only thing we can say is: "yes, it is possible in SQL".
Now you will say "how?" and the answer will be "we need a test case to build the SQL and show it".
It is not to bother people we ask some things, it is because they are needed to help in an accurate way.

Re: Generate SQL via SQL [message #683827 is a reply to message #683825] Tue, 23 February 2021 06:18 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
OK, new try Wink

So I have two tables, called tab_data and tab_rules:
create table tab_data ( 
    id number constraint tab_data_pk primary key, 
    val varchar2(200), 
    dim_1 number, 
    dim_2 number, 
    dim_3 number, 
    dim_4 number, 
    dim_5 number, 
    dim_6 number, 
    dim_7 number 
)

create table tab_rules ( 
    id number constraint tab_rules_pk primary key, 
    dim_id number, 
    name varchar2(200), 
    type number,
    rule varchar2(100) 
)
I insert data in that two tables:
INSERT INTO tab_data 
VALUES (1, NULL, 11, 22, 33, 44, 55, 66, 71);
INSERT INTO tab_data 
VALUES (2, 5000, 11, 22, 33, 44, 55, 66, 72);
INSERT INTO tab_data 
VALUES (3, 2000, 11, 22, 33, 44, 55, 66, 73);

INSERT INTO tab_rules 
VALUES (71, 7, 'Value A+B', 4, '({7@72} * 1) + ({7@73} * -1)');
INSERT INTO tab_rules 
VALUES (72, 7, 'Value A', 1, NULL);
INSERT INTO tab_rules 
VALUES (73, 7, 'Value B', 1, NULL);
Now I'm looking for a SELECT statement to get the value of 7000 (addition of 5000 and 2000) for tab_data-ID 1. The rule for that row can be found in tab_rules, where column rule contains the formula for adding ID 72 and 73. These IDs are again to be looked for in column dim_7 because tab_rules.dim_id is 7. By the way: Column tab_rules.type defines whether the value is calculated or not.
Re: Generate SQL via SQL [message #683828 is a reply to message #683827] Tue, 23 February 2021 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah ah! that's different from the original question.
Originally the result would be: "This string should result in a SELECT...", now you want the result of this SELECT.
And the original SELECT refers to only one table "tab_data" which has now no "dim_id" column but it is in tab_rules table.

Is this of any use that ids in tab_rules are same than dim_7 values? Or is it just a bad luck?

Also: "These IDs are again to be looked for in column dim_7 because tab_rules.dim_id is 7", so what is the "7" before "@" in the formula? Do we look up at this 7 (original question) or at the 7 in tab_rules.dim_id (last sentence)?
Is is important that all dim_id is 7 in tab_rules? Is the dim_id for type 4 used or not?
...

Your test case and explanation are quite confusing, I hope my post explain in what.

Re: Generate SQL via SQL [message #683831 is a reply to message #683827] Tue, 23 February 2021 08:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
Registered: January 2010
Location: Connecticut, USA
Senior Member
mikewazowski wrote on Tue, 23 February 2021 07:18

Now I'm looking for a SELECT statement to get the value of 7000 (addition of 5000 and 2000) for tab_data-ID 1.
Well, you maybe looking to get 7000 but it isn't what your formula says: ({7@72} * 1) + ({7@73} * -1) so result would be 5000 - 2000 = 3000.
Anyway, something like:

SELECT  RULE,
        XMLCAST(
                XMLQUERY(
                         '/ROWSET/ROW/VAL'
                         PASSING DBMS_XMLGEN.GETXMLTYPE(
                                                        'SELECT  ' ||
                                                        REPLACE(
                                                                REPLACE(
                                                                        REPLACE(
                                                                                RULE,
                                                                                '{',
                                                                                '(SELECT VAL FROM TAB_DATA WHERE DIM_'
                                                                               ),
                                                                        '@',
                                                                        ' = '
                                                                       ),
                                                                '}',
                                                                ')'
                                                               ) ||
                                                             ' VAL FROM DUAL'
                                                       )
                         RETURNING CONTENT
                        )
                AS NUMBER
               ) RULE_RESULT
  FROM  TAB_RULES
  WHERE RULE IS NOT NULL
/

RULE                           RULE_RESULT
------------------------------ -----------
({7@72} * 1) + ({7@73} * -1)          3000

SQL>
Re: Generate SQL via SQL [message #683832 is a reply to message #683828] Tue, 23 February 2021 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also: " get the value of 7000 (addition of 5000 and 2000) " but the formula indicates 5000 - 2000 due to the -1 factor.

Re: Generate SQL via SQL [message #683834 is a reply to message #683831] Tue, 23 February 2021 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah OK, you chose to ignore the sentence: " These IDs are again to be looked for in column dim_7 because tab_rules.dim_id is 7."

Re: Generate SQL via SQL [message #683835 is a reply to message #683828] Tue, 23 February 2021 08:39 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
I'll try to elaborate a little. We have an application in which the user can define fields that are calculated from other fields. In my example, the element with tab_rules.id = 71 is calculated by adding the IDs 72 and 73 together. That's why the formula '({7@72} * 1) + ({7@73} * -1)' is in tab_rules.rule.

When accessing this calculated field, I now want to determine tab_data.val. To do this, I have to process all dim_*-columns, but for our example I will limit myself to dim_7. There, tab_data.dim_7 contains the value 71 (for tab_data.id = 1).

With this ID 71, I go to the table tab_rules and select the data set with tab_rules.id = 7. In the column tab_rules.rule I then find the string '({7@72} * 1) + ({7@73} * -1)'. (Short note on this: This 'syntax' can still be adapted so that the SELECT might be easier to implement).

Now it's time to analyse the string. Between { and }, the individual formula components are defined, in my example two (for the addition of these). The 7 (in front of the @) defines that I have to look in tab_data into the column dim_7 (and not dim_1 or dim_2 etc.) and search there for the ID 72 (which is behind the @) in order to select the value from tab_data.val. Thus I get the values 5000 and 2000 for the example. These are added and calculated with each other: 5000 * 1 + 2000 * -1 = 3000.

And I would like to determine this with a SELECT, if this is possible (e.g. to carry out the calculation again via trigger when the values change).

Is this written in an understandable way?

Best regards, Michael
Re: Generate SQL via SQL [message #683837 is a reply to message #683835] Tue, 23 February 2021 08:42 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Oh, you already answered while I was writing so much Smile

I will test this in the next few days and get back to you.

Additional question: Do you know if this SQL would also work in MS SQL and MySQL (maybe with slightly different syntax)?
Re: Generate SQL via SQL [message #683840 is a reply to message #683837] Tue, 23 February 2021 09:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
Registered: January 2010
Location: Connecticut, USA
Senior Member
mikewazowski wrote on Tue, 23 February 2021 09:42

Additional question: Do you know if this SQL would also work in MS SQL and MySQL (maybe with slightly different syntax)?
You will have to find replacemenmt for DBMS_XMLGEN logic.

SY.
Re: Generate SQL via SQL [message #683842 is a reply to message #683835] Tue, 23 February 2021 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So no more tab_rule.dim_id.
Here's another way (I modified a little bit your tables and data to avoid confusion):
SQL> select * from tab_data order by id;
        ID        VAL      DIM_1      DIM_2      DIM_3      DIM_4      DIM_5      DIM_6      DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       1000         11         21         31         41         51         61         71
         2       5000         12         22         32         42         52         62         72
         3       2000         13         23         33         43         53         63         73

3 rows selected.

SQL> select * from tab_rules order by id;
        ID     DIM_ID NAME                       TYPE RULE
---------- ---------- -------------------- ---------- --------------------------------------------------
       101            Value A+B                     4 ({7@72} * 1) + ({7@73} * -1)
       102            Value A+B/C                   4 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)
       103            Value (A+B)/C                 4 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3)
       104            Value A                       1
       105            Value B                       1

5 rows selected.

SQL> with
  2    elems as (
  3      select id, rule, column_value fnb,
  4             regexp_substr(rule, '\(+[^)]+\)+', 1, column_value) f,
  5             regexp_substr(rule, '\) *([^) ]+)', 1, column_value, null, 1) ope
  6      from tab_rules,
  7           table(cast(multiset(select level column_value from dual
  8                               connect by level <= regexp_count(rule,'@'))
  9                      as sys.odciNumberList))
 10      where type = 4
 11    ),
 12    bits as (
 13      select id fid, rule, fnb, f,
 14             regexp_substr(f,'^\(+') deb,
 15             regexp_substr(f,'{(\d+)', 1, 1, null, 1) colnb,
 16             regexp_substr(f,'@(\d+)', 1, 1, null, 1) did,
 17             regexp_substr(f,'}(.*$)', 1, 1, null, 1) fin,
 18             ope
 19      from elems
 20    )
 21  select fid, rule,
 22         to_number(
 23           extractvalue(
 24             dbms_xmlgen.getXMLtype(
 25               'select '||
 26               listagg(deb||'(select val from tab_data where dim_'||colnb||'='||did||')'
 27                       ||fin||ope)
 28                 within group (order by fnb)||
 29               ' val from dual'),
 30            '/ROWSET/ROW/VAL')) val
 31  from bits
 32  group by fid, rule
 33  order by fid
 34  /
       FID RULE                                                      VAL
---------- -------------------------------------------------- ----------
       101 ({7@72} * 1) + ({7@73} * -1)                             3000
       102 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)         1001.66667
       103 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3)       1.83333333

3 rows selected.
Solomon's query gives, of course, the same result:
SQL> SELECT  RULE,
  2          XMLCAST(
  3                  XMLQUERY(
  4                           '/ROWSET/ROW/VAL'
  5                           PASSING DBMS_XMLGEN.GETXMLTYPE(
  6                                                          'SELECT  ' ||
  7                                                          REPLACE(
  8                                                                  REPLACE(
  9                                                                          REPLACE(
 10                                                                                  RULE,
 11                                                                                  '{',
 12                                                                                  '(SELECT VAL FROM TAB_DATA WHERE DIM_'
 13                                                                                 ),
 14                                                                          '@',
 15                                                                          ' = '
 16                                                                         ),
 17                                                                  '}',
 18                                                                  ')'
 19                                                                 ) ||
 20                                                               ' VAL FROM DUAL'
 21                                                         )
 22                           RETURNING CONTENT
 23                          )
 24                  AS NUMBER
 25                 ) RULE_RESULT
 26    FROM  TAB_RULES
 27    WHERE RULE IS NOT NULL
 28  /
RULE                                               RULE_RESULT
-------------------------------------------------- -----------
({7@72} * 1) + ({7@73} * -1)                              3000
(({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3)        1.83333333
({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)          1001.66667

3 rows selected.

[Updated on: Tue, 23 February 2021 09:23]

Report message to a moderator

Re: Generate SQL via SQL [message #683845 is a reply to message #683837] Tue, 23 February 2021 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mikewazowski wrote on Tue, 23 February 2021 15:42
Oh, you already answered while I was writing so much Smile

I will test this in the next few days and get back to you.

Additional question: Do you know if this SQL would also work in MS SQL and MySQL (maybe with slightly different syntax)?
You can use Solomon inner expressions to generate the queries and then execute them:
SQL> select 'SELECT  ' ||
  2         REPLACE(
  3           REPLACE(
  4             REPLACE(
  5               RULE,
  6               '{',
  7               '(SELECT VAL FROM TAB_DATA WHERE DIM_'
  8             ),
  9             '@',
 10             ' = '
 11          ),
 12          '}',
 13          ')'
 14        ) ||
 15        ' VAL FROM DUAL'
 16        sqls
 17  from tab_rules
 18  where type = 4
 19  order by id
 20  /
SQLS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT  ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 72) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 73) * -1) VAL FROM DUAL
SELECT  ((SELECT VAL FROM TAB_DATA WHERE DIM_1 = 11) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_2 = 22) * 2) / ((SELECT VAL FROM TAB_DATA WHERE DIM_3 = 33) * 3) VAL FROM DUAL
SELECT  (((SELECT VAL FROM TAB_DATA WHERE DIM_1 = 11) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_2 = 22) * 2)) / ((SELECT VAL FROM TAB_DATA WHERE DIM_3 = 33) * 3) VAL FROM DUAL

3 rows selected.
Re: Generate SQL via SQL [message #683846 is a reply to message #683845] Tue, 23 February 2021 09:41 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Thank you very, very, very much. You are great, Michel and Solomon. I will try to understand your code and test it on my database. I will come back - with questions or the success message Wink
Re: Generate SQL via SQL [message #683847 is a reply to message #683845] Tue, 23 February 2021 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This makes me think that with 19c (in fact starting from 12c), you can avoid the use of dbms_xmlgen:
SQL> with
  2    function exec (str varchar2) return number
  3    is
  4      ret number;
  5    begin
  6      execute immediate str into ret;
  7      return ret;
  8    end;
  9  select id, rule,
 10         exec ('SELECT '||
 11               REPLACE(
 12                 REPLACE(
 13                   REPLACE(RULE, '{', '(SELECT VAL FROM TAB_DATA WHERE DIM_'),
 14                   '@', ' = '),
 15                 '}', ')') ||
 16               ' VAL FROM DUAL')
 17          val
 18  from tab_rules
 19  where type = 4
 20  order by id
 21  /
        ID RULE                                                      VAL
---------- -------------------------------------------------- ----------
       101 ({7@72} * 1) + ({7@73} * -1)                             3000
       102 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)         1001.66667
       103 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3)       1.83333333

3 rows selected.

[Updated on: Tue, 23 February 2021 10:05]

Report message to a moderator

Re: Generate SQL via SQL [message #683876 is a reply to message #683847] Mon, 01 March 2021 14:19 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Hello,

unfortunately my test has been delayed a little. With the last variant (WITH FUNCTION ...) I get along in principle quite well.

Since I did not create the database and tables myself, I am working my way into the data step by step. In doing so, I have now discovered that the other dimensions of a data set (columns DIM_*) must always be considered as well. So if it says 7@72, that means I'm looking for the 72 in DIM_7 - just like it already works in your implementations so far. But in addition I have to set the other six dimensions equal for both datasets.

If I try to find DIM_7-ID 72 for the calculation of DIM_7-ID 71, the other DIM-IDs must match in both records.

I try it with an example:

select * from tab_data order by id;
        ID        VAL      DIM_1      DIM_2      DIM_3      DIM_4      DIM_5      DIM_6      DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       NULL         11         21         31         41         51         61         71
         2       5000         11         21         31         41         51         61         72
         3       2000         11         21         31         41         51         61         73
         4       NULL         12         22         32         42         52         62         71
         5      50000         12         22         32         42         52         62         72
         6      20000         12         22         32         42         52         62         73
ID 1 = 3000
ID 4 = 30000

This means the WHERE condition always consists of seven DIM-comparisons, but only the DIM-column from the {x@y} formulation is searched with value, for the remaining columns the values must match. How do I get this queried dynamically?

Best regards, Michael
Re: Generate SQL via SQL [message #683877 is a reply to message #683876] Tue, 02 March 2021 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not really clear.
See my formula "({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)"; what does match to what?

Till now, it was simple:
Apply the formula using "val" from:
- row (tell it "row 1") with "dim_1=11" for part 1,
- row with "dim_2=22" for part 2,
- row with "dim_3=33" for part 3.
What is added now in this example?

Re: Generate SQL via SQL [message #683879 is a reply to message #683877] Tue, 02 March 2021 05:22 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
I try with another example:

select * from tab_data order by id;
        ID        VAL      DIM_1      DIM_2      DIM_3      DIM_4      DIM_5      DIM_6      DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       NULL         11         21         31         41         51         61         71
         2       5000         11         21         31         41         51         61         72
         3       2000         11         21         31         41         51         61         73
         4       NULL         12         22         32         42         52         62         71
         5      50000         12         22         32         42         52         62         72
         6      20000         12         22         32         42         52         62         73
         7       NULL         13         23         33         43         53         61         70
         8      10000         13         23         33         43         53         62         70
         9       2000         13         23         33         43         53         63         70
ID 1 in tab_data refers to formula
({7@72} * 1) + ({7@73} * -1)
, ID 4 the same. ID 7 refers to formula
({6@62} * 1) / ({6@63} * 1)
.
The results would be:
1: 5000 - 2000 = 3000
4: 50000 - 20000 = 30000
7: 10000 / 2000 = 5

The SELECT now needs to be like that
1:
SELECT  ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 72 AND dim_1 = 11 AND dim_2 = 21 AND dim_3 = 31 AND dim_4 = 41 AND dim_5 = 51 AND dim_6 = 61) * 1) +
        ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 73 AND dim_1 = 11 AND dim_2 = 21 AND dim_3 = 31 AND dim_4 = 41 AND dim_5 = 51 AND dim_6 = 61) * -1) VAL FROM DUAL
2:
SELECT  ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 72 AND dim_1 = 12 AND dim_2 = 22 AND dim_3 = 32 AND dim_4 = 42 AND dim_5 = 52 AND dim_6 = 62) * 1) +
        ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 73 AND dim_1 = 12 AND dim_2 = 22 AND dim_3 = 32 AND dim_4 = 42 AND dim_5 = 52 AND dim_6 = 62) * -1) VAL FROM DUAL
3:
SELECT  ((SELECT VAL FROM TAB_DATA WHERE DIM_6 = 62 AND dim_1 = 13 AND dim_2 = 23 AND dim_3 = 33 AND dim_4 = 43 AND dim_5 = 53 AND dim_7 = 70) * 1) +
        ((SELECT VAL FROM TAB_DATA WHERE DIM_6 = 63 AND dim_1 = 13 AND dim_2 = 23 AND dim_3 = 33 AND dim_4 = 43 AND dim_5 = 53 AND dim_7 = 70) * -1) VAL FROM DUAL
That is, the first WHERE condition refers to tab_rules.rule and the remaining six conditions refer to the associated values in tab_data.dim_*. So I have to read the first condition from the rule and determine the other six (which always depend on the first one, because it is not always dim_7) dynamically.
Re: Generate SQL via SQL [message #683881 is a reply to message #683879] Tue, 02 March 2021 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please answer for MY example.

Re: Generate SQL via SQL [message #683882 is a reply to message #683881] Tue, 02 March 2021 06:17 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Oh, sorry. Your example won't happen.
Each formula in tab_rules.rule always refers to exactly one dimension, i.e. to one dim_*-column. So the formula is always only dim_6 or only dim_7 etc.

So the difference from my original query is that in addition to the dim_*-column defined by the formula, six more dim columns must be included in the WHERE condition.
Re: Generate SQL via SQL [message #683885 is a reply to message #683882] Tue, 02 March 2021 08:21 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
My current idea goes in the following direction. I link all seven DIM columns according to the known logic:
SELECT '{1@' || dim_1 || '}{2@' || dim_2 || '}{3@' || dim_3 || '}{4@' || dim_4 || '}{5@' || dim_5 || '}{6@' || dim_6 || '}{7@' || dim_7 || '}'
  FROM tab_data;
The results will be:
ID1: {1@11}{2@21}{3@31}{4@41}{5@51}{6@61}{7@71}
ID2: {1@11}{2@21}{3@31}{4@41}{5@51}{6@61}{7@72}
ID3: {1@11}{2@21}{3@31}{4@41}{5@51}{6@61}{7@73}
ID4: {1@12}{2@22}{3@32}{4@42}{5@52}{6@62}{7@71}
ID5: {1@12}{2@22}{3@32}{4@42}{5@52}{6@62}{7@72}
ID6: {1@12}{2@22}{3@32}{4@42}{5@52}{6@62}{7@73}
ID7: {1@13}{2@23}{3@33}{4@43}{5@53}{6@61}{7@70}
ID8: {1@13}{2@23}{3@33}{4@43}{5@53}{6@62}{7@70}
ID9: {1@13}{2@23}{3@33}{4@43}{5@53}{6@63}{7@70}
Now I would have to replace "only" the corresponding field depending on the rule. So to calculate ID1, I have to replace {7@71} with {7@72} and {7@73}, because that stands for 71 in the rule:
({7@72} * 1) + ({7@73} * -1)
What do you think about that? How can I implement this dynamically?

Thanks, Michael
Re: Generate SQL via SQL [message #683888 is a reply to message #683885] Tue, 02 March 2021 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I try with another example:

Please post the INSERT statements for this example.

Re: Generate SQL via SQL [message #683889 is a reply to message #683888] Tue, 02 March 2021 12:36 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
With pleasure Smile

INSERT INTO tab_data 
VALUES (1, NULL, 11, 21, 31, 41, 51, 61, 71);
INSERT INTO tab_data 
VALUES (2, 5000, 11, 21, 31, 41, 51, 61, 72);
INSERT INTO tab_data
VALUES (3, 2000, 11, 21, 31, 41, 51, 61, 73);
INSERT INTO tab_data
VALUES (4, NULL, 12, 22, 32, 42, 52, 62, 71);
INSERT INTO tab_data 
VALUES (5, 50000, 12, 22, 32, 42, 52, 62, 72);
INSERT INTO tab_data 
VALUES (6, 20000, 12, 22, 32, 42, 52, 62, 73);
INSERT INTO tab_data
VALUES (7, NULL, 13, 23, 33, 43, 53, 61, 70);
INSERT INTO tab_data
VALUES (8, 10000, 13, 23, 33, 43, 53, 62, 70);
INSERT INTO tab_data
VALUES (9, 2000, 13, 23, 33, 43, 53, 63, 70);
INSERT INTO tab_rules 
VALUES (61, 6, 'Value X/Y', 4, '({6@62} * 1) / ({6@63} * 1)');
INSERT INTO tab_rules 
VALUES (71, 7, 'Value A+B', 4, '({7@72} * 1) + ({7@73} * -1)');
Re: Generate SQL via SQL [message #683890 is a reply to message #683889] Tue, 02 March 2021 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For this, I moved all the code in the inline function:
SQL> select * from tab_data order by id;
        ID        VAL      DIM_1      DIM_2      DIM_3      DIM_4      DIM_5      DIM_6      DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1                    11         21         31         41         51         61         71
         2       5000         11         21         31         41         51         61         72
         3       2000         11         21         31         41         51         61         73
         4                    12         22         32         42         52         62         71
         5      50000         12         22         32         42         52         62         72
         6      20000         12         22         32         42         52         62         73
         7                    13         23         33         43         53         61         70
         8      10000         13         23         33         43         53         62         70
         9       2000         13         23         33         43         53         63         70

9 rows selected.

SQL> select * from tab_rules order by id;
        ID     DIM_ID NAME                       TYPE RULE
---------- ---------- -------------------- ---------- ------------------------------
       101            Value A+B                     4 ({7@72} * 1) + ({7@73} * -1)
       110            Value A/B                     4 ({6@62} * 1) / ({6@63} * 1)

2 rows selected.

SQL> with
  2    function exec (rule varchar2, dims sys.odcinumberlist)
  3      return number
  4    is
  5      col pls_integer;
  6      que varchar2(32760);
  7      ret number;
  8    begin
  9      que := regexp_replace(rule,
 10               '{(\d+)@(\d+)}',
 11               '(SELECT val FROM tab_data d2 WHERE dim_\1 = \2 AND val IS NOT NULL #\1#)');
 12      col := to_number (regexp_substr (que, '#(\d+)#', 1, 1, null, 1));
 13      que := replace (que, '#'||col||'#', '#');
 14      for i in 1..7 loop
 15        if i != col then
 16          que := replace (que, '#', ' and dim_'||i||' = '||dims(i)||'#');
 17        end if;
 18      end loop;
 19      que := 'SELECT '||replace(que, '#', '')||' val FROM DUAL';
 20      execute immediate que into ret;
 21      return ret;
 22    end;
 23    compute as (
 24      select r.id rule_id, r.rule, d.id data_id,
 25             exec (rule,
 26                   sys.odcinumberlist(dim_1, dim_2, dim_3, dim_4, dim_5, dim_6, dim_7))
 27              val
 28      from tab_rules r, tab_data d
 29      where r.type = 4
 30        and d.val is null
 31    )
 32  select rule_id, rule, data_id, val
 33  from compute
 34  where val is not null
 35  order by rule_id, data_id
 36  /
   RULE_ID RULE                              DATA_ID        VAL
---------- ------------------------------ ---------- ----------
       101 ({7@72} * 1) + ({7@73} * -1)            1       3000
       101 ({7@72} * 1) + ({7@73} * -1)            4      30000
       110 ({6@62} * 1) / ({6@63} * 1)             7          5

3 rows selected.
Re: Generate SQL via SQL [message #683891 is a reply to message #683890] Tue, 02 March 2021 23:48 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Oh wow, this looks much more complicated at first sight than the first idea Wink But many, many thanks for that.

At first I couldn't get it to work for me. In lines 23 to 31 you define compute. There you join the two tables tab_data and tab_rule, but in my eyes the connection between both tables is missing, isn't it? I get "no data found".

To understand exec, I have to learn about regular expressions, but I can do that. Other databases should be able to handle this (SQLServer, MySQL), the only question is whether they can work with FUNCTION within the WITH statement.

Best regards, Michael
Re: Generate SQL via SQL [message #683892 is a reply to message #683891] Wed, 03 March 2021 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but in my eyes the connection between both tables is missing, isn't it?

There is none.
From your examples, I inferred you want to check every rule of type 4 with every data with null val.
Some of these data rows won't match with some rules and so they gets a NULL result, this is why there is an intermediate "compute" inline view which computes all results and NULL are filtered out in the main part.
You can replace the "compute" in the last part by its definition in "compute" part without any change:
select <blabla> from (<something>)
is equivalent to
with compute as (<something>) select <blabla> from compute

You can replace the lines 9 to 13 with expressions containing only REPLACE, SUBSTR and INSTR functions.

[Updated on: Wed, 03 March 2021 14:54]

Report message to a moderator

Re: Generate SQL via SQL [message #683893 is a reply to message #683892] Wed, 03 March 2021 01:19 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Thank you for your help and the detailed descriptions. I will take my time to look at everything and try to understand it - unfortunately it will not fit until next week. Of course I will get back to you when I have understood it or if new questions arise. Thank you a thousand times, Michael
Re: Generate SQL via SQL [message #683895 is a reply to message #683893] Wed, 03 March 2021 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's the version without regular expressions and "compute":
SQL> with
  2    function exec (rule varchar2, dims sys.odcinumberlist)
  3      return number
  4    is
  5      col pls_integer;
  6      que varchar2(32760);
  7      ret number;
  8    begin
  9      -- Retrieve the dim column in rule (first one is taken as all are same)
 10      col := to_number (substr (rule,
 11                                instr (rule, '{') + 1,
 12                                instr (rule, '@') - instr (rule, '{') - 1));
 13      -- Convert the rule to the query expression to execute
 14      que := replace (
 15               replace (
 16                 replace (rule, '{', '(SELECT VAL FROM TAB_DATA WHERE DIM_'),
 17                 '@', ' = '),
 18               '}', ' AND val IS NOT NULL #)');
 19      -- Add conditions on the non-rule dim columns
 20      for i in 1..7 loop
 21        if i != col then
 22          que := replace (que, '#', ' AND dim_'||i||' = '||dims(i)||'#');
 23        end if;
 24      end loop;
 25      -- Complete the query
 26      que := 'SELECT '||replace(que, '#', '')||' val FROM DUAL';
 27      -- Execute the query
 28      execute immediate que into ret;
 29      return ret;
 30    end;
 31  select rule_id, rule, data_id, val
 32  from ( select r.id rule_id, r.rule, d.id data_id,
 33                exec (rule,
 34                      sys.odcinumberlist(dim_1, dim_2, dim_3, dim_4, dim_5, dim_6, dim_7))
 35                 val
 36         from tab_rules r, tab_data d
 37         where r.type = 4
 38           and d.val is null )
 39  where val is not null
 40  order by rule_id, data_id
 41  /
   RULE_ID RULE                              DATA_ID        VAL
---------- ------------------------------ ---------- ----------
       101 ({7@72} * 1) + ({7@73} * -1)            1       3000
       101 ({7@72} * 1) + ({7@73} * -1)            4      30000
       110 ({6@62} * 1) / ({6@63} * 1)             7          5

3 rows selected.

[Updated on: Wed, 03 March 2021 02:32]

Report message to a moderator

Re: Generate SQL via SQL [message #683988 is a reply to message #683895] Mon, 15 March 2021 03:17 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Hello, it has now taken a little longer Wink

Now that I have managed the selection - thanks to your help - I would like to create a trigger based on the same principle, which adjusts the data of other records in this table when a value in tab_data is changed.

So if a value is changed, which has influence on another data set due to a rule (in which VAL is calculated from the changed data set), this other data set should also be updated.

Unfortunately, I seem to have a problem understanding between BEFORE and AFTER UPDATE triggers. Theoretically it can happen that several data sets are changed with one update, therefore I need (according to my understanding) a trigger with the statement FOR EACH ROW.

With the AFTER UPDATE TRIGGER I would have expected that after the update is done everything is possible on the same table. But it comes the ORA-04091, which probably means that I am not allowed to work on tab_data, because the trigger reacts on this table. In my example, I have not even performed an update, but only selected so far.

So which trigger do I have to use to adjust by changing a value (tab_data.val) all dependent entries in tab_data which have a connection via tab_rules. It must be ensured that several values are updated with one UPDATE. In addition it can be naturally that the data record connected by tab_rules leads then also again to an update, because this value is also again part of a rule in a third data record.

My example:
CREATE OR REPLACE TRIGGER trg_tab_data_upd_val
AFTER UPDATE OF val ON tab_data
FOR EACH ROW
DECLARE
    que CLOB;--VARCHAR2(32767);
    ret NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('ID: ' || :new.id || ', Value: ' || :new.val);
    FOR rec IN (SELECT r.id AS rule_id, r.rule rule_txt, r.dimension_id,
                       sys.odcinumberlist(d.dim_1, d.dim_2, d.dim_3, d.dim_4, d.dim_5, d.dim_6, d.dim_7) dim_lst
                  FROM tab_rules r, tab_data d
                 WHERE r.type = 4)
    LOOP
        que := REPLACE(REPLACE(REPLACE(rec.rule_txt, '{', '(SELECT NVL(val, 0) FROM tab_data WHERE dim_'), '@', ' = '), '}', ' #)');
	FOR i IN 1..7 LOOP
            IF i != rec.dimension_id THEN
                que := REPLACE(que, '#', ' AND dim_'||i||' = '||rec.dim_lst(i)||'#');
            END IF;
	END LOOP;
	que := 'SELECT '||REPLACE(que, '#', '')||' val FROM dual';
        --EXECUTE IMMEDIATE que INTO ret;
        DBMS_OUTPUT.PUT_LINE( rec.rule_id || ': ' || rec.rule_txt || ' = ' || ret || ' >> ' || que);
    END LOOP;
END;
Re: Generate SQL via SQL [message #683995 is a reply to message #683988] Tue, 16 March 2021 15:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But it comes the ORA-04091, which probably means that I am not allowed to work on tab_data, because the trigger reacts on this table

This is correct, you cannot read the table you are currently modifying inside the trigger which handles this modification.
Oracle does not allow this and the reason is clear: the result could be non-deterministic which means that if you execute twice the same UPDATE on the same table data you could have a different result inside the table depending on the order the rows are processed.
And you tell it yourself with "In addition it can be naturally that the data record connected by tab_rules leads then also again to an update, because this value is also again part of a rule in a third data record."

Needing this also means your data model is not correct.

[Updated on: Tue, 16 March 2021 15:42]

Report message to a moderator

Re: Generate SQL via SQL [message #684005 is a reply to message #683995] Thu, 18 March 2021 02:45 Go to previous messageGo to next message
mikewazowski
Messages: 15
Registered: February 2021
Junior Member
Yes, the data model is certainly not optimal, but unfortunately it is out of my hands. I honestly still don't understand why there is an AFTER update trigger, which apparently doesn't work after the update, but apparently still within the update process. But never mind.

I'm now trying to transfer the issue to the application (i.e. before the database processing). Maybe it works better there Wink

Thanks so far, I have learned a lot in any case.
Re: Generate SQL via SQL [message #684006 is a reply to message #684005] Thu, 18 March 2021 03:25 Go to previous message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the trigger runs AFTER the update has processed (that is after rows are updated) but still inside the UPDATE statement.
The execution plan of an UPDATE is:
  • Start of UPDATE statement
  • BEFORE STATEMENT triggers
  • BEFORE EACH ROW to update triggers
  • Update rows
  • AFTER EACH ROW to update triggers
  • AFTER STATEMENT triggers
  • End of UPDATE statement
Triggers are part of the DML statements.

Note that you can SELECT the table you are modifying in a STATEMENT trigger but not in a EACH ROW one:
SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       NUMBER(38)

SQL> create or replace trigger t_after after update on t for each row
  2  declare
  3    s pls_integer;
  4  begin
  5    select sum(val) into s from t;
  6    dbms_output.put_line('Sum is '||s);
  7  end;
  8  /

Trigger created.

SQL> update t set val=2*val;
update t set val=2*val
       *
ERROR at line 1:
ORA-04091: table MICHEL.T is mutating, trigger/function may not see it
ORA-06512: at "MICHEL.T_AFTER", line 4
ORA-04088: error during execution of trigger 'MICHEL.T_AFTER'


SQL> create or replace trigger t_before before update on t -- for each row
  2  declare
  3    s pls_integer;
  4  begin
  5    select sum(val) into s from t;
  6    dbms_output.put_line('Before sum is '||s);
  7  end;
  8  /

Trigger created.

SQL> create or replace trigger t_after after update on t -- for each row
  2  declare
  3    s pls_integer;
  4  begin
  5    select sum(val) into s from t;
  6    dbms_output.put_line('After sum is '||s);
  7  end;
  8  /

Trigger created.

SQL> update t set val=2*val;
Before sum is 30
After sum is 60

20 rows updated.
But you cannot modify inside the trigger, just read.
Previous Topic: Generating Installment rows
Next Topic: how to use select field value in where clause (merged)
Goto Forum:
  


Current Time: Tue Apr 13 18:30:11 CDT 2021