Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Passing Column name as a variable

Posted on 2014-10-13
5
Medium Priority
?
183 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

722 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