Passing Column name as a variable

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
FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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
FutureDBA-Author Commented:
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
johnsoneSenior Oracle DBACommented:
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
FutureDBA-Author Commented:
I am using Oracle Apex as the front end that will process this.
0
johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.