Solved

Passing Column name as a variable

Posted on 2014-10-13
5
180 Views
Last Modified: 2014-11-17
Hi Experts,

I have a pivot view that pivots based on the week of the year.

I want to pass a variable to select the week of the year and do some basic arithmetic for the following weeks.

Right now, the query that I have works as follows and gives me the correct data.
WITH Q AS 
  ( 
    SELECT
      *
    FROM
      REPORT_PIVOT_BRANDS
    ORDER BY BRAND ASC
  )
          SELECT
            "36" Base,
            "36" W1,
            "36"-"37" W1_DIFF,
            "36" Base,
            "38" W2,
            "36"-"38" W2_DIFF,
            "36" Base, 
            "39" W3,
            "36"-"39" W3_DIFF,
            "36" Base,
            "40" W4,
            "36"-"40" W4_DIFF
          FROM Q;

Open in new window


What i would like to do is something like this
WITH Q AS 
  ( 
    SELECT
      *
    FROM
      REPORT_PIVOT_BRANDS
    ORDER BY BRAND ASC
  )
          SELECT
            :base Base,
            :base + 1 W1,
            :base - (:base + 1) W1_DIFF,
            :base Base,
            :base + 2 W2,
            :base - (:base + 2) W2_DIFF,
            :base Base,
            :base + 3 W3,
            :base - (:base + 3) W3_DIFF,
            :base Base,
            :base + 4 W4,
            :base - (:base + 4) W4_DIFF
          FROM Q;

Open in new window


But when I do that, it passes my results as actual numbers instead of selecting the column names
0
Comment
Question by:FutureDBA-
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40377419
You cannot pass column names in like that.  The column and table names need to be available at parse time, and the value of bind variables would not be available at parse time.

Also, not sure why you need the WITH clause,  you original query should be the same as this:

SELECT "36"        Base, 
       "36"        W1, 
       "36" - "37" W1_DIFF, 
       "36"        Base, 
       "38"        W2, 
       "36" - "38" W2_DIFF, 
       "36"        Base, 
       "39"        W3, 
       "36" - "39" W3_DIFF, 
       "36"        Base, 
       "40"        W4, 
       "36" - "40" W4_DIFF 
FROM   report_pivot_brands 
ORDER  BY brands ASC; 

Open in new window

0
 

Author Comment

by:FutureDBA-
ID: 40377482
How can I achieve what I am trying to do?

my :base might by 36, another day i might need to look at week 22, another day i might need to look at week 40.

What is the easiest way to achieve what I am trying to achieve ?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40377791
You would have to construct the query as a string in whatever language you are processing this in.  Then execute the query.  A type of dynamic SQL.
0
 

Author Comment

by:FutureDBA-
ID: 40378112
I am using Oracle Apex as the front end that will process this.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 40378172
I don't know Apex that well.  However, this link seems to have a pretty good example of how to build and execute dynamic SQL in Apex.

https://community.oracle.com/thread/1091115?start=0&tstart=0
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Listener Not Starting 11 63
make null the repeated levels 2 36
Oracle 12c Default Isolation Level 17 54
pl/sql parameter is null sometimes 2 23
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question