Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #184311] Wed, 26 July 2006 02:16 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi everybody,


CREATE TABLE BH_TEMP(dd DATE);

INSERT INTO BH_TEMP VALUES('01-JAN-2006');
INSERT INTO BH_TEMP VALUES('01-JAN-2005');
INSERT INTO BH_TEMP VALUES('01-JUN-2006');
INSERT INTO BH_TEMP VALUES('01-JUN-2005');
INSERT INTO BH_TEMP VALUES('01-SEP-2006');
INSERT INTO BH_TEMP VALUES('01-SEP-2005');
INSERT INTO BH_TEMP VALUES('01-DEC-2006');


SELECT 'Q'||TO_CHAR(dd,'Q') Quarter,SUM(DECODE(to_char(dd,'YYYY'),to_char(trunc(sysdate),'YYYY'),1,0)),
SUM(DECODE(to_char(dd,'YYYY'),to_char(add_months(trunc(sysdate),-12),'YYYY'),1,0))
FROM BH_TEMP
GROUP BY TO_CHAR(dd,'Q')
HAVING TO_CHAR(dd,'Q') IS NOT NULL


QUARTER SUM(DECODE(TO_CHAR(DD,'YYYY'), SUM(DECODE(TO_CHAR DD,'YYYY'),_1
Q1 1 1
Q2 1 1
Q3 1 1
Q4 1 0

Everything is right except the Year Heading.

I want to display the Column Header as Actual year whatever is coming out from to_char(trunc(sysdate),'YYYY') but no the hard coded one's like '2006'. It should be dynamic whatever comes out of to_char(trunc(sysdate),'YYYY').


Quarter 2006 2005
Q1 1 1
Q2 1 1
Q3 1 1
Q4 1 0


Thanks in advance
Re: Query [message #184523 is a reply to message #184311] Wed, 26 July 2006 21:52 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

TRY THIS

SELECT 'Q'||TO_CHAR(dd,'Q') Quarter,SUM(DECODE(to_char(dd,'YYYY'),to_char(trunc(sysdate),'YYYY'),1,0)) 2006,
SUM(DECODE(to_char(dd,'YYYY'),to_char(add_months(trunc(sysdate),-12),'YYYY'),1,0)) 2005
FROM BH_TEMP
GROUP BY TO_CHAR(dd,'Q')
HAVING TO_CHAR(dd,'Q') IS NOT NULL

HOPE IT WORKS
Re: Query [message #184540 is a reply to message #184311] Thu, 27 July 2006 00:08 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
I dont want the hard coded year 2006 or 2005. I want is the dynamic year coming out of the to_char(sysdate,'yyyy').


Thanks
Re: Query [message #184559 is a reply to message #184540] Thu, 27 July 2006 01:14 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
i dont think that changing the column header dynamically in ordinary SQL is possible. please anyone correct me if i'm wrong

Thanks,
Thangam
Re: Query [message #184572 is a reply to message #184559] Thu, 27 July 2006 02:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you are running this query in Pl/Sql, you could generate it as a dynamic ref cursor, which would let you specify the column name (although that's not much use)
declare
  type ty_refc is ref cursor;
  
  c_rc        ty_refc;
  v_col_name  varchar2(30);
begin
  v_col_name := 'Col_name';
  open c_rc for 'SELECT sysdate '||v_col_name||' from dual';
end;


If you are running this as part of a script in SQL*Plus, you can specify the column name as a substitution variable
SQL> select sysdate &name from dual;
Enter value for name: Col_name
old   1: select sysdate &name from dual
new   1: select sysdate Col_name from dual

COL_NAME
--------------------
27-Jul-2006 07:54:54


If this query is just part of vanilla pl/sql, then I don't think you can do it - pl/sql likes to have all its columns declared in advance for queries (weak ref cursors not withstanding)
Re: Query [message #184650 is a reply to message #184311] Thu, 27 July 2006 06:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What abou this Splendid method by 'Art Metzer'

SQL> CREATE OR REPLACE PROCEDURE P11 (p_rc OUT SYS_REFCURSOR)
  2  AS
  3     l_sql VARCHAR2(32700) := 'SELECT ''Q''||TO_CHAR(dd,''Q'') Quarter '  ;
  4     l_rc  SYS_REFCURSOR;
  5  BEGIN
  6     FOR c IN (SELECT DISTINCT TO_CHAR(DD,'YYYY') yr FROM   BH_TEMP)
  7     LOOP
  8        l_sql := l_sql
  9                || ' ,SUM(DECODE(TO_CHAR(bt.DD,''YYYY''),'''
 10                ||  c.yr
 11                || ''',1,0)) "'
 12                ||  c.yr
 13                || '"  ';
 14     END LOOP;
 15        l_sql := l_sql
 16                || 'FROM  BH_TEMP bt GROUP BY TO_CHAR(dd,''Q'')';
 17          OPEN p_rc FOR l_sql;
 18   END P11;
 19  /

Procedure created.
Procedure created.

SQL> VARIABLE rc REFCURSOR
SQL> EXEC p11(:rc);

PL/SQL procedure successfully completed.

SQL> PRINT :rc

QU       2005       2006
-- ---------- ----------
Q1          1          1
Q2          1          1
Q3          1          1
Q4          0          1

SQL>


Thumbs Up
Rajuvan

[Updated on: Thu, 27 July 2006 06:14]

Report message to a moderator

Re: Query [message #184655 is a reply to message #184650] Thu, 27 July 2006 06:35 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
thanks Rajuvan


But is it not possible in SQL?
Re: Query [message #184657 is a reply to message #184655] Thu, 27 July 2006 06:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


I dont think so ...

Rajuvan.
Re: Query [message #184658 is a reply to message #184657] Thu, 27 July 2006 06:50 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
Ok thanks alot

Re: Query [message #184770 is a reply to message #184311] Thu, 27 July 2006 20:39 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

write a function which returns later part of sysdate and store the value in a variable for example...

x=substr(sysdate,7,len(sysdate))

use 'x' as a column alias in the query....

not sure about this

hope it works
Re: Query [message #184802 is a reply to message #184770] Fri, 28 July 2006 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you're not sure about it, why don't you give it a try.

Thst way you'd actually be able to tell if the advice you're giving is any good, or if you're just wasting the OPs time.

Don't you think it would be a good idea to only offer advice when you know it works?

Why do you think we post so many examples showing that things do work?

I'll give you a clue:

What you've sugested doesn't work. At all.

Also, your code for getting the year out of sysdate is utterly depenent on your current nls_date_format, and won't work for anything other then dd-mm-yyyy or mm-dd-yyyy.
Re: Query [message #184950 is a reply to message #184572] Fri, 28 July 2006 19:13 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

well jrowbottom,

This is a forum, if things doesn't work out for you then just ignore and get on with what ever you get.

no matter how the solution is...

it doesn't really matter if the solution makes sense or not....

one thing you have to understand is that you have to appreciate for what ever you are given. Be professional when you are discussing something in a forum.

if you do not know how to reply for the messages which are trying to help you out then there is no point in discussing about it.

i am sorry if it doesn't work. Try to learn or teach and enhance your knowledge.

forum is the best medium to learn or teach things just try to make best use of it. share your ideas and enhance your knowledge, don't just think about what you want.

make sure your messages doesn't hurt anyone's feelings.

thanx for your time.


Re: Query [message #185014 is a reply to message #184950] Sun, 30 July 2006 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> set verify off
SQL> column this_year new_value _this_year
SQL> column prev_year new_value _prev_year
SQL> select to_char(sysdate, 'yyyy')      as this_year
  2  ,      to_char(sysdate, 'yyyy') - 1  as prev_year
  3  from   dual
  4  /

THIS  PREV_YEAR
---- ----------
2006       2005

SQL> SELECT 'Q'||TO_CHAR(dd,'Q') Quarter
  2  ,      SUM(DECODE(to_char(dd,'YYYY'),to_char(trunc(sysdate),'YYYY'),1,0)) "&_this_year"
  3  ,      SUM(DECODE(to_char(dd,'YYYY'),to_char(add_months(trunc(sysdate),-12),'YYYY'),1,0)) "&_prev_year"
  4  FROM   BH_TEMP
  5  GROUP  BY TO_CHAR(dd,'Q')
  6  HAVING TO_CHAR(dd,'Q') IS NOT NULL
  7  /

QU       2006       2005
-- ---------- ----------
Q1          5          5
Q3          5          5
Q2          5          5
Q4          5          0


[Edit: posted script instead of results]

[Updated on: Sun, 30 July 2006 01:10]

Report message to a moderator

Re: Query [message #185083 is a reply to message #185014] Sun, 30 July 2006 23:24 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
Thank you so much frank.

This can be done.


Thanks
Re: Query [message #185338 is a reply to message #184950] Tue, 01 August 2006 05:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm terribly sorry if you have difficulty accepting criticism. Had I known this in advance, it would have changed my posting not at all.

It is not my question that you were attempting to answer - my reply to your posting was to help other people avoid wasting time trying something that was doomed to fail, and to point out to you that there are gaps in your knowledge that you could usefully address.

One point I feel I must address. You write
Quote:

it doesn't really matter if the solution makes sense or not....


I must disagree. It really does matter. If a solution is nonsense, then it is not worth posting, unless you are asking for help to make it work.

I will be leaving this little spat here.
Previous Topic: import ... PK & FK relation
Next Topic: viewing triggers and procedures
Goto Forum:
  


Current Time: Fri Dec 09 17:22:25 CST 2016

Total time taken to generate the page: 0.21603 seconds