CODEWITHSUNDEEP
sqldb2
Tone
Tone

Reputation: 783

Select from VALUES

I have three function calls in which I need all records in one column. I have been unable to achieve this and would appreciate a push in the right direction. I'm using a db2 database.

Function calls:

schema1.Usage ('element', 20140101, 20140714)
schema1.TOTAL_Usage ('element', 20140101, 20140714)
schema1.Usage ('element', 20140101, 20140714)/schema1.TOTAL_Usage ('element', 20140101, 20140714)

Here is what I have tried with no success:

select * from  
(VALUES schema1.Usage ('element', 20140101, 20140714)  
    ,schema1.TOTAL_Usage ('element', 20140101, 20140714)  
    ,schema1.Usage ('element', 20140101, 20140714)/schema1.TOTAL_Usage ('element', 20140101, 20140714) AS X(a);

Upvotes: 0

Views: 235

Answers (3)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Your query should be fine with just a few small adjustments, I assume X is 1 row with 3 columns and not the other way around

select * from (
    VALUES ( schema1.Usage ('element', 20140101, 20140714)  
           , schema1.TOTAL_Usage ('element', 20140101, 20140714)  
           , schema1.Usage ('element', 20140101, 20140714) 
              / schema1.TOTAL_Usage ('element', 20140101, 20140714)
           )
) AS X(a,b,c);

Perhaps it would be better to move the division to outer select:

select a, b, a/b from (
    VALUES ( schema1.Usage ('element', 20140101, 20140714)  
           , schema1.TOTAL_Usage ('element', 20140101, 20140714) )
) AS X(a,b);

Upvotes: 0

xQbert
xQbert

Reputation: 35343

Based on http://oscarvalles.wordpress.com/2013/03/29/select-without-table-in-db2/

Assuming this works:

Values schema1.Usage('element', 20140101, 20140714)

However if you want all values back you may be able to concatenate them into a single string... but not multiple referenced columns

then maybe...

Values schema1.Usage('element', 20140101, 20140714) & ' ' & 
       schema1.TOTAL_Usage('element', 20140101, 20140714) & ' ' & 
       schema1.CAPABILITY_Usage('element', 20140101, 20140714)/schema1.TOTAL_Usage ('element', 20140101, 20140714)

Or what Gordon has posted... as referenced http://www.dbforums.com/db2/975637-dual-table-db2-udb.html

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If these are scalar functions, just put them in the select:

select schema1.Usage('element', 20140101, 20140714),
       schema1.TOTAL_Usage('element', 20140101, 20140714)
       schema1.CAPABILITY_Usage('element', 20140101, 20140714)/schema1.TOTAL_Usage('element', 20140101, 20140714) AS X(a)
from sysibm.sysdummy1;

Upvotes: 1

Related Questions