Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing Column name as a variable

Posted on 2014-10-13
5
Medium Priority
?
185 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 2000 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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

916 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