Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to create formulas in an SQL statement
Comments
-
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
-
> michaels> WITH t AS
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.
(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'
) -
Am I hearing some discomfort there? What's the problem?
-
You are really good in that area - as usual.
Regards.
Satyaki De. -
Am I hearing some discomfort there? What's theIt is undeniably very tricky, but I have two problems:
problem?
(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. -
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 formdisputableJust a matter of taste and consuetude
This discussion has been closed.