Re: dynamically determining the field to select

From: Eddie <ra3masis_at_gmail.com>
Date: 21 Jul 2004 15:06:36 -0700
Message-ID: <cdmpdc$hsa@odah37.prod.google.com>

hastenthunder wrote:
> Hi SQL Masters,
>
> I'm trying to perform a rather simple query, but the field I'm
selecting
> from the table can only be determined at run time, basing on the
value of
> some other field in the same table. The logic is like:
>
>
> IF (year_of_sales < 1995) THEN
> SELECT fieldA
> FROM myTable;
> ELSE IF (year_of_sales >1995 and year_of_sales< 1998) THEN
> SELECT fieldB
> FROM myTable;
> ELSE
> SELECT fieldC
> FROM myTable;
> END IF;
>
>
> The table looks like:
>
> Create tabe myTable (year_of_sales number(4),
> fieldA varchar2(5),
> fieldB varcahr2(5),
> fieldC varchar2(5));
>
>
> Can someone help me to put the above query into a single SQL
statement? Is
> it possible?
>
> Thanks a lot.

I have tested the following under 8.1.7:

CREATE TABLE myTable (
year_of_sales number(4),

fieldA varchar2(5),
fieldB varchar2(5),
fieldC varchar2(5))

/
INSERT INTO myTable VALUES(1990, 'a1', 'b1', 'c1') /
INSERT INTO myTable VALUES(1994, 'a2', 'b2', 'c2') /
INSERT INTO myTable VALUES(1996, 'a3', 'b3', 'c3') /
INSERT INTO myTable VALUES(1998, 'a4', 'b4', 'c4') /
INSERT INTO myTable VALUES(1999, 'a5', 'b5', 'c5') /
INSERT INTO myTable VALUES(2003, 'a6', 'b6', 'c6') /
SELECT
year_of_sales,
CASE
WHEN year_of_sales < 1995 THEN fieldA
WHEN year_of_sales > 1995 AND year_of_sales < 1998 THEN fieldB ELSE fieldC
END AS field
FROM myTable
/

The result looks like this:

1990	a1
1994	a2
1996	b3
1998	c4
1999	c5
2003	c6

Regards,
Eddie. Received on Wed Jul 21 2004 - 17:06:36 CDT