Refer to Column Name within SQL

[Elysium]

Here's what I'm trying to accomplish:

I have a query with two columns. The first column can be one of three types (Year, Quarter, Month) and the second column holds the number of business days according to the time period in column one. We are using a program called Siebel Analytics that employs column selectors on the first column, allowing us to change the view from Year to Month and so on.

Enough of the background. I've racked my head to find a dynamic way to see what time period is in the first column and adjust the second column accordingly. Is there some way to grab the field name for column one? If I had my way, the SQL would look something like this:

SELECT Time."Year", CASE WHEN [First Column Name] = "Time.'Year'" THEN Time.'Num of Bus Days Year' WHEN [First Column Name] = "Time.'Quarter'" THEN Time.'Num of Bus Days Quarter' ELSE Time.'Num of Bus Days Month' END FROM DWH

Is this possible?

Regards,

Randy

Randy

 

[carp]

Randy -
Do you mean something like:

11:39:33 SQL> select * from dwh;

PERIOD_TYP NUM_DAYS
---------- ----------
Year 200
Quarter 58
Month 45

UPDATE dwh
   SET num_days = CASE period_type
                   WHEN 'Year' THEN 365
                   WHEN 'Quarter' THEN 90
                   WHEN 'Month' THEN 30
                  END;

11:41:15 SQL> select * from dwh;

PERIOD_TYP NUM_DAYS
---------- ----------
Year 365
Quarter 90
Month 30

This was done w/ Oracle 9i. What RDBMS are you using?

 

[Elysium]

Carp,

Thanks for responding. Oracle is the RDBMS. What I need to do is find out what the first column's name is so that I can choose the correct Business Days element. Year, Quarter and Month are not choices in field 1. Rather they are types of time elements. Hopefully, the following text will explain things better:

 Year      Bus Days
------    ----------
2002      254
2003      253
2004      254

-- OR --

 Quarter      Bus Days
---------    ----------
01           64
02           63
03           65
04           64

-- OR --

 Month      Bus Days
-------    ----------
01         22
02         19
03         23
etc.

The field that becomes "Year", "Quarter" or "Month" is dynamic. I need to know how to reference the field's name within the same SQL. Make sense?

Randy

 

[carp]

Not quite.
1. What is the table's structure - columns/datatypes?
2. Where does this "time element" come from?
3. Is SQL an absolute requirement, or would a stored function that you could invoke be acceptable?

To all readers - yes, I know we're in danger of departing from ANSI SQL here. Please bear with us.

 

[Elysium]

Here's some background:

We are using Siebel Analytics Server as our Data Warehouse front-end. It has a feature called "Column Selectors" that allows us to choose, dynamically, what a certain field should be, i.e. either Yearly, Quarterly or Monthly. That way, we can write one query for three (3) different Time Views. When a user selects "Year" from a drop-down list the first column becomes Time."Year". If they select "Month", the first column becomes Time."Month". I need to know what the column changes to so that I can return the correct strata of Business Days.

Thank you for taking time with this, and if it can't be solved, so be it. I just want to exhaust all options before I give up.

Randy

 

[carp]

OK, so Siebel has a what sounds like a combo box or some such mechanism that allows you to select a time period. Is this on a web page, or java application, or what? And once you have selected your period (e.g., 'Month'), does it launch a direct query or what? What I really need to know is what is this feature sending to the database (and how), and how are you catching the values?

Perhaps I'm being dense today - I've got several plates spinning right now - so this may require patience on your part!

 

[Elysium]

The front-end is web-based and I'm unsure of the native language, but I believe it's C.

Once you select the Time View from the combo box, the data is refreshed (as is the underlying SQL) to reflect what was chosen.

Beyond this front-end, I cannot see what's going on. I thought there may be some sort of SQL syntax I could pass in. My hands are in the air at this point.

Randy

 

[carp]

It sounds like it's something that you can't really touch. If you look at the parameters that get passed in with the URL, you might be able to tell what kind of values are being passed in.
If Siebel doesn't provide any documentation with this, then you are probably stuck with whatever they give you. That DOESN'T mean that you can't build an equivilent capability for yourself. But I suspect that will NOT involve ANSI SQL, so you probably ought to open a new thread on the appropriate Oracle forum if you want to press on.
In the meantime, maybe somebody who is smarter about this than I am will happen along.
Good luck!

 

[Elysium]

Thanks!

Randy