Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

How to create formulas in an SQL statement

2»

Comments

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    I have a nasty feeling there is in XQuery expressions, but I'm not going there.
    ;-)
    michaels>  WITH t AS
         (SELECT 1 ID, 1 x, 2 y FROM DUAL UNION ALL
          SELECT 2,    3,   4   FROM DUAL UNION ALL
          SELECT 3,    5,   6   FROM DUAL)
    -----      
    SELECT   *
        FROM XMLTable('declare function local:a($a,$b)
                       {
                         ($a + $b)
                       }; (: eof :)
                       for $i in /ROWSET/ROW
                       return <ROW>
                              <ID>{$i/ID}</ID>
                              <X>{$i/X}</X>
                              <Y>{$i/Y}</Y>
                              <A>{local:a($i/X,$i/Y)}</A>
                              <B>{local:a($i/X,$i/Y) + 2}</B>
                              </ROW>' PASSING XMLTYPE(CURSOR(SELECT * FROM t))
                       COLUMNS ID NUMBER PATH 'ID',
                                X NUMBER PATH 'X',
                                Y NUMBER PATH 'Y',
                                A NUMBER PATH 'A',
                                B NUMBER PATH 'B'
                       )
    
            ID          X          Y          A          B
    ---------- ---------- ---------- ---------- ----------
             1          1          2          3          5
             2          3          4          7          9
             3          5          6         11         13
  • 537882
    537882 Member Posts: 744
    edited May 30, 2007 5:18AM
    > 
    michaels> WITH t AS
    (SELECT 1 ID, 1 x, 2 y FROM DUAL UNION ALL
    SELECT 2, 3, 4 FROM DUAL UNION ALL
    SELECT 3, 5, 6 FROM DUAL)

    *
    FROM XMLTable('declare function local:a($a,$b)
    {
    ($a + $b)
    }; (: eof :)
    for $i in /ROWSET/ROW
    return <ROW>
    <ID>{$i/ID}</ID>
    <X>{$i/X}</X>
    <Y>{$i/Y}</Y>
    <A>{local:a($i/X,$i/Y)}</A>
    <B>{local:a($i/X,$i/Y) + 2}</B>
    </ROW>' PASSING XMLTYPE(CURSOR(SELECT
    * FROM t))
    COLUMNS ID NUMBER PATH 'ID',
    X NUMBER PATH 'X',
    Y NUMBER PATH 'Y',
    A NUMBER PATH 'A',
    B NUMBER PATH 'B'
    )
    Thank goodness for the revolution of XML in the database, so that we may all be enabled to use such conveniences to enhance the maintainability of our code.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Am I hearing some discomfort there? What's the problem?
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    You are really good in that area - as usual.

    Regards.

    Satyaki De.
  • 537882
    537882 Member Posts: 744
    edited May 30, 2007 7:44AM
    Am I hearing some discomfort there? What's the
    problem?
    It is undeniably very tricky, but I have two problems:

    (1) By defining an inline function the declarative nature of SQL is subverted, since you are defining a procedural element.

    (2) It has low aesthetic form.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    lol ..ok, I expected some serious refutations (like perfomance ;) )
    (1) By defining an inline function the declarative nature of SQL is subverted, since you are defining a procedural element.
    Not very different then e.g. for model version or any other way using regular plsql functions in SQL e.g.. An expression like »a[cv()]« has to be considered as procedural as well.
    If you want, even built-in SQL functions are - well - procedural (put input, get output) after all ;)
    (2) It has low aesthetic form
    disputable ;) Just a matter of taste and consuetude
This discussion has been closed.