CODEWITHSUNDEEP
db2
max092012
max092012

Reputation: 407

Fetch weekly data as a row based on monthly date range in DB2

Is there a way to dynamically add the week ending date as column header in query from a monthly date range ? I have written the below query and its bringing in the desired result 

select metername , sum(delta) from TABLE where metername='abc'
 AND (readingdate >= DATE ('2020-04-01')  AND readingdate < DATE ('2020-04-30') + 1 DAY)
 group by metername, week(readingdate);

Current Output

CURRENT OUTPUT

Expected output - Also the heading should have the week ending date

output

Upvotes: 0

Views: 190

Answers (1)

Samuel Pizarro
Samuel Pizarro

Reputation: 308

No, you cant! at least not in a SELECT statement.

the column/alias name by definition is a SQL Identifier.

AS new-column-name Names or renames the result column. The name must not be qualified and does not have to be unique. new-column-name is an SQL identifier of 128 UTF-8 bytes or less.

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_selectclause.html

And a SQL identifier, it's just a fixed name. can not be a result of an expression as you want. https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000720.html

As @Mark Barinstein suggested, you would need a SP for this.

Upvotes: 1

Related Questions