Solved

Passing Column name as a variable

Posted on 2014-10-13
5
177 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 34

Expert Comment

by:johnsone
Comment Utility
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-
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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-
Comment Utility
I am using Oracle Apex as the front end that will process this.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now