January 8, 2004 - 4pm Central time zone
Reviewer: A reader
January 8, 2004 - 4pm Central time zone
Reviewer: A reader
Tom,
What tool/utility do you use to load table data examples in your db??
Thanks.
Followup January 8, 2004 - 8pm Central time zone:
unfortunately, it is cut and paste and vi.
I edit what they provide here.
I LOVE it when people give me inserts, table creates and so on!!!
tiny fix
January 8, 2004 - 8pm Central time zone
Reviewer: Marcio from br
on calendar script
column month format a20
Followup January 9, 2004 - 8am Central time zone:
thanks :)
Hmmm...
January 8, 2004 - 9pm Central time zone
Reviewer: A reader
I find it interesting that Riaz had been waiting for a long time to ask Tom a question, and yet
when he finally got the chance all he could come up with was silly questions! Just pulling your leg
Riaz...
January 8, 2004 - 10pm Central time zone
Reviewer: A reader
!!!Note to Everybody !!!
"Please provide inserts, table creates and so on "
Thanks ...
Ok
January 9, 2004 - 12am Central time zone
Reviewer: Ram from OH,USA
Dear sir,
why don't you use
$exec owa_util.calendarprint ?
Bye!
Followup January 9, 2004 - 8am Central time zone:
cause that would be HTML?
Regarding Question. 3
January 9, 2004 - 7am Central time zone
Reviewer: Riaz Shahid from PRAL, Lahore, Pakistan
Tom ! Actually what i needed in Question 3 was a output like:
Month: Jan 2004
S M T W T F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 30
Month: Feb 2004
S M T W T F S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29
And So on....
Followup January 9, 2004 - 8am Central time zone:
uh huh.
did you run the script supplied?????????
ops$tkyte@ORA920PC> break on month skip 1
ops$tkyte@ORA920PC> set linesize 20
ops$tkyte@ORA920PC> column month format a20
ops$tkyte@ORA920PC> select lpad( Month, 20-(20-length(month))/2 ) month,
2 "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
3 from (
4 select to_char(dt,'fmMonthfm YYYY') month,
5 to_char(dt+1,'iw') week,
6 max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
7 max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
8 max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
9 max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
10 max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
11 max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
12 max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
13 from ( select trunc(sysdate,'y')-1+rownum dt
14 from all_objects
15 where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
16 )
17 group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
18 )
19 order by to_date( month, 'Month YYYY' ), to_number(week)
20 /
MONTH
--------------------
Su Mo Tu We Th Fr Sa
-- -- -- -- -- -- --
January 2004
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
February 2004
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29
......
and if the extra lines bother you -- just put it into a plsql "for loop" and use dbms_output to
format it however pleases you most.
Why I am not getting output like You showed ?
January 9, 2004 - 9am Central time zone
Reviewer: Riaz Shahid from PRAL, Lahore, Pakistan
Hello Tom !
Sorry for bothering but i am not getting the output as yours one.
Instead i get like this:
SQL> break on month skip 1
SQL> set linesize 20
SQL> column month format a20
SQL> select lpad( Month, 20-(20-length(month))/2 ) month,
2 "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
3 from (
4 select to_char(dt,'fmMonthfm YYYY') month,
5 to_char(dt+1,'iw') week,
6 max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
7 max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
8 max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
9 max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
10 max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
11 max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
12 max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
13 from ( select trunc(sysdate,'y')-1+rownum dt
14 from all_objects
15 where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
16 )
17 group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
18 )
19 order by to_date( month, 'Month YYYY' ), to_number(week)
20 /
MONTH
====================
Su
====================
Mo
====================
Tu
====================
We
====================
Th
====================
Fr
====================
Sa
====================
January 2004
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
February 2004
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
MONTH
====================
Su
====================
Mo
====================
Tu
====================
We
====================
Th
====================
Fr
====================
Sa
====================
29
March 2004
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
April 2004
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MONTH
====================
Su
====================
Mo
====================
Tu
====================
We
====================
Th
====================
Fr
====================
Sa
====================
18
(and so on....)
20 rows selected.
Elapsed: 00:00:09.38
SQL> spo off
Followup January 9, 2004 - 9am Central time zone:
run with a clean login.sql file -- you must have other (unknown to me) settings.
but as I said, you can use use dbms_output with "set server serveroutput on format wrapped" to get
what EVER you want.
add a
set pagesize 9999
in there as well.
January 9, 2004 - 12pm Central time zone
Reviewer: A reader
For output extra lines calendar.
January 9, 2004 - 8pm Central time zone
Reviewer: Marcio from br
from your calendar script I left my linesize with 131 and got this:
ops$marcio@MARCI9I1> @cal
MONTH Su Mo Tu We Th Fr Sa
-------------------- -- -- -- -- -- -- --
January 2004 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
February 2004 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29
March 2004 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
....
Followup January 10, 2004 - 10am Central time zone:
yup, i was trying to emulate the unix 'cal' command
9i?
January 12, 2004 - 10am Central time zone
Reviewer: George from Chicago
I ran in 9i and got similar results to Riaz's:
MONTH
--------------------
Su
----------------------------------------------------------------------------------------------------
-------------------------------
Mo
----------------------------------------------------------------------------------------------------
-------------------------------
Tu
----------------------------------------------------------------------------------------------------
-------------------------------
We
----------------------------------------------------------------------------------------------------
-------------------------------
Th
----------------------------------------------------------------------------------------------------
-------------------------------
Fr
----------------------------------------------------------------------------------------------------
-------------------------------
Sa
----------------------------------------------------------------------------------------------------
-------------------------------
January 2004
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Works like a charm in 8i
Followup January 12, 2004 - 1pm Central time zone:
you have cursor_sharing=force set on so all of the constants "disappear"
Help with query ...
January 12, 2004 - 5pm Central time zone
Reviewer: Anand from Austin, Texas
I have a table COUNTRY :
SQL> select * from country;
CID COUNTRY_NAME
---------- --------------------
1 USA
2 CANADA
that contains country names.
and another table STATE :
SQL> select * from state;
SID CID STATE_NAME
---------- ---------- --------------------
1 1 TEXAS
2 1 VIRGINIA
3 1 CALIFORNIA
1 2 ONTARIO
that contains names of states under a country in the COUNTRY table.
I am looking for a query that would display the country name and all the states under that
particular coutry in a single row like :
USA Virginia Texas Alabama California
Canada Ontario Quebec
How can I achieve this?
Thanks
!!!
January 12, 2004 - 5pm Central time zone
Reviewer: Christo Kutrovsky from Ottawa, ON Canada
>>>> you have cursor_sharing=force set on so all of the constants "disappear"
Now I give 5 stars for figuring this out, with close to no information. I am most impressed with
your telepathic skills.
Followup January 13, 2004 - 1am Central time zone:
(having seen this about 1,000,000 times by now helps too :)
Excellent
January 13, 2004 - 2am Central time zone
Reviewer: Riaz Shahid from PRAl, Lahore, Pakistan
Now I've found why the same query wasn't providing same results as that of yours. Actually i had
cursor_sharing set to force.when i set it back to exact, It worked and provided me the same
results.
Thanks for your great help...
RE: Help with query ... (Anand)
January 13, 2004 - 6am Central time zone
Reviewer: Marcio from br
Anand you can find it out on this site, just search
to "stragg".
To do this in 9i
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402
To do this in 8i
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:229614022562
Like this in 9i:
ops$t_mp00@MRP9I1> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 )
25 /
Type created.
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN string_agg_type,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := ltrim(self.total,',');
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT string_agg_type,
32 ctx2 IN string_agg_type)
33 return number
34 is
35 begin
36 self.total := self.total || ctx2.total;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /
Type body created.
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> select country_name, state_name
2 from country natural join state
3 /
COUNTRY_NA STATE_NAME
---------- ------------------------------
USA TEXAS
USA VIRGINIA
USA CALIFORNIA
CANADA ONTARIO
4 rows selected.
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> column state_name format a30 word_wrap
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> select country_name, stragg(state_name) state_name
2 from country natural join state
3 group by country_name
4 /
COUNTRY_NA STATE_NAME
---------- ------------------------------
CANADA ONTARIO
USA TEXAS,VIRGINIA,CALIFORNIA
2 rows selected.
Please Clarify
January 13, 2004 - 11am Central time zone
Reviewer: Reader from Columbus, OH USA
Tom
Please explain
' you have cursor_sharing=force set on so all of the constants "disappear"
'
Regards
Followup January 13, 2004 - 5pm Central time zone:
search for
cursor_sharing force
on this site.... to see what it means/does....
ops$tkyte@ORA9IR2> alter session set cursor_sharing=force;
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select 'hello', 'world', 55 from dual;
'HELLO' 'WORLD' 55
-------------------------------- -------------------------------- ----------
hello world 55
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text
2 like 'select %, %, % from dual';
SQL_TEXT
----------------------------------------------------------------------------------------------------
-------------------------------
select :"SYS_B_0", :"SYS_B_1", :"SYS_B_2" from dual
cursor sharing is an "autobinder", a crutch for bad programmers....
Answer to Reader from Columbus, OH USA
January 13, 2004 - 3pm Central time zone
Reviewer: Christo Kutrovsky from Ottawa, ON Canada
To: Reader from Columbus, OH USA
Let me try to explain, Tom can correct me if I am wrong.
Because Oracle no longer nows that "Su" is can only be at max 2 caraters long.
Consider this:
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
Becomes:
max(decode(to_char(dt,:"SYS_B_06"),:"SYS_B_07",lpad(to_char(dt,:"SYS_B_08"),:"SYS_B_09"))) "Su"
See how the second parameter of lpad, '2' is replaced with :"SYS_B_09", so the width of the column
becomes unknown, thus defaults to max possible. sqlplus uses the with to format the query, thus he
formats them with a possibility of 4000 caraters.
Consider this example:
SQL> create table XX (a varchar2(5));
Table created.
SQL> insert into XX values ('abc');
1 row created.
SQL> select * from XX;
A
-----
abc
SQL> alter table XX modify a varchar2(50);
Table altered.
SQL> select * from XX;
A
--------------------------------------------------
abc
See how in the first there were 5 '-' caraters, in the second there are '50'. Well, think about
4000.
I hope this helps.
Followup January 13, 2004 - 5pm Central time zone:
yup....
Cursor Sharing and the column width
January 13, 2004 - 10pm Central time zone
Reviewer: Henry Yick from Hong Kong
I'm impressed with the finding you've made on this. In my 9i environment, nomatter I set the
cursor_sharing to EXACT or FORCE, the column width of the day is 4 all the time. Like that:
MONTH
--------------------
Su Mo Tu We
---- ---- ---- ----
Th Fr Sa
---- ---- ----
January 2004
1 2 3
4 5 6 7
8 9 10
11 12 13 14
15 16 17
....
Although a few column formating can solve the problem. But what is the clue of this behaviour?
Regards
Followup January 14, 2004 - 1am Central time zone:
perhaps you have a multi-byte character set.
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'
what is the output of that.
Cursor Sharing and column width
January 14, 2004 - 2am Central time zone
Reviewer: Henry Yick from Hong Kong
Exactly! You are right. Mine is a multi-byte system and that's why. Thanks so much.
For the second question, I have tried the query in EXACT mode and retry the same query by changing
the cursor_sharing to FORCE mode on session level. I belive the system will not perform autobinding
and just use the cached statement if there's an exact match(with constant). That's why the result
is the same even in FORCE mode.
Correct me if I'm wrong.
I had to change
January 14, 2004 - 11am Central time zone
Reviewer: Christo Kutrovsky from Ottawa, ON Canada
I had to change the query (adding a dummy space somewhere) to force oracel to hard-parse it again,
otherwise the force/exact change has no effect.
Thank you, Marcio
January 16, 2004 - 10am Central time zone
Reviewer: Anand from Austin, Texas
Thanks , Marcio. It works like a charm.
February 13, 2004 - 7am Central time zone
Reviewer: A reader
Logic and Strategies
March 22, 2004 - 6pm Central time zone
Reviewer: Hien from South Australia
Tom
I am refering to the cal emulation script. Brilliant!
Could you please share with us what is the strategy to come up such a script. The script somewhat
looks complex but it produces such elegant output.
Perhaps you can explain step-by-step how you came up with the script.
Thank heaps.
Hien
Followup March 22, 2004 - 9pm Central time zone:
I'll have to write "zen and the art of sql" someday -- hard to explain -- but if you start with the
innermost inline view, I started there and built out iteratively.
select lpad( Month, 20-(20-length(month))/2 ) month,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
from (
select to_char(dt,'fmMonthfm YYYY') month,
to_char(dt+1,'iw') week,
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' ), to_number(week)
/
I started with the set of dates for the year:
select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
then I "pivoted" using a max/decode routine I use lots...
then I used sqlplus to format it a tiny bit...
Another Misc.Question
March 23, 2004 - 12am Central time zone
Reviewer: Ram from Bangalore,KA,India
Dear Sir,
What may be wrong with the following query?
SQL> select deptno,max_sal,(select ename from emp where sal = max_sal and
2 rownum = 1) max_ename,min_sal,(select ename from emp where sal =
3 min_sal and rownum = 1) min_ename from(
4 select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
5 group by deptno)
6 /
select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression
Please do reply.
Followup March 23, 2004 - 7am Central time zone:
1 select deptno,max_sal,(select ename from emp where sal = max_sal and
2 rownum = 1) max_ename,min_sal,(select ename from emp where sal =
3 min_sal and rownum = 1) min_ename from(
4 select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
5* group by deptno)
scott@ORA9IR2> /
DEPTNO MAX_SAL MAX_ENAME MIN_SAL MIN_ENAME
---------- ---------- ---------- ---------- ----------
10 5000 KING 1300 MILLER
20 3000 SCOTT 800 SMITH
30 2850 BLAKE 950 JAMES
runs "fine" for me in 8.0.6 and up.....
Nice
March 27, 2004 - 5am Central time zone
Reviewer: Ram from Bangalore,KA,India
Hi Tom,
What do the group functions Variance and Stddev provide?
SQL> select variance(sal) from emp;
VARIANCE(SAL)
-------------
1398313.87
SQL> select stddev(sal) from emp;
STDDEV(SAL)
-----------
1182.50322
I have used them but don'n know what they represent?
Could you please explain?
Bye!
Followup March 27, 2004 - 10am Central time zone:
google em. they are standard statistical functions. they come from "math"
(if you don't know what they are, you probably don't need them)
Statistical measures
March 27, 2004 - 11am Central time zone
Reviewer: Jim from wilsonville, OR
standard deviation is the square root of the varience. They are statistical measures of how
tightly or loosly a series of measurements "group" around a mean. (average) If you have ever heard
the expression in school "Graded on a curve" it refers to adjusting grades to follow a statistical
bell curve (usually what is refered to as a normal distribution). (there are a ton of other
distributions used for a variety of things)
Tom is correct, if you don't know what they are then you probably don't need them. However, they
are important.
OK
March 28, 2004 - 12pm Central time zone
Reviewer: Kumar from Pune,India
Hi Tom,
What is a multicolumn subquery?Where they can be useful?
Bye!
Followup March 28, 2004 - 5pm Central time zone:
select * from emp
where (deptno,job) in ( select deptno, job from some_other_table );
try getting the right answer without using a multi-column subquery.... That is where they are
useful.
Small bug due to "iw" format in cal query
August 16, 2005 - 11am Central time zone
Reviewer: Sean D Stuber from Columbus, OH USA
the 'iw' format will return 53 for January 1, 2005, thus messing up the sorting. Try sorting by
weekends instead...
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
FROM (
SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Su",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mo",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tu",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "We",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Th",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fr",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sa",
NEXT_DAY(dt-1,'SATURDAY') weekend
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM ALL_OBJECTS
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), NEXT_DAY(dt-1,'SATURDAY')
)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), weekend
Calendar SQL
June 4, 2007 - 5am Central time zone
Reviewer: Reena
Tom,
Your code for generating calendar has been most useful to me.I have a question.
in a previous thread you said
<quote>
I started with the set of dates for the year:
select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
</quote>
My question is :
why do you use the table all_objects for generating the set of dates for the year ?(what if it doesnt have the number of rows to satisfy our query criteria) Just wondering...Is there any other way to do it ?
Thanks in advance.
Reena
Followup June 5, 2007 - 8am Central time zone:
all_objects has thousands of rows, it is highly unlikely not to have enough rows for this purpose.
in 9iR2 and above, you can use:
ops$tkyte%ORA10GR2> with data as (select trunc(sysdate,'y')-1+level dt
2 from dual
3 connect by level <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
4 select *
5 from data
6 /
DT
---------
01-JAN-07
02-JAN-07
03-JAN-07
04-JAN-07
...
29-DEC-07
30-DEC-07
31-DEC-07
365 rows selected.
Wowwwwwwwwwww!!!!
June 5, 2007 - 10am Central time zone
Reviewer: Jay from Herndon, VA
Tom,
How on earth did you come up with this logic for the calendar??? I'm still trying to figure out what exactly you are doing and I have the code right in front of me :-(
This is complex!!!
Great job! I hope you or Sean will explain the logic for us. That would be very helpful :-O
Sean was right about the sorting problem that was happening with your code.
This is cool stuff Tom. Thanks a ton.
Mo or Su for start of week?
July 7, 2007 - 8am Central time zone
Reviewer: MH from NLD
Tom,
Referring to the calendar query: it's GREAT!
One question tough:
I found out that my daynumbers differ from yours, so I needed to change the column prompts.
I.e.:
your
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
mine
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Mo"
Q: What's causing this difference?
Checking and playing with values for v$nls_parameters,
nls_session_parameters, nls_database_parameters
(DUTCH vs. AMERICAN for NLS_DATE_LANGUAGE and NLS_LANGUAGE) didn't make any difference..was I changing the wrong parameters?
I ran your query on:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Many thanks for your time and great site.
Regards,
Martijn
OK
December 13, 2007 - 9am Central time zone
Reviewer: Ramesh from PA,USA
Hi Tom,
I have a table a with c as int column with column length of 8.
I have data in this table 1,22,567,9999.
I would like to do an LPAD so that 0 gets prefixed to column values so that
length of column values equal to default column length as per data dictionary.
SQL> create table a(c number(8))
2 /
Table created.
SQL> insert into a values(12)
2 /
1 row created.
SQL> insert into a values(223)
2 /
1 row created.
SQL> insert into a values(4567)
2 /
1 row created.
SQL> insert into a values(4)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from a
2 /
C
----------
12
223
4567
4
4 rows selected.
SQL> select t1.*,lpad(c,<<col length>>,'0')
2 from a t1
3 /
How to arrive at the <<col length>>?
Any idea you can give?
Followup December 13, 2007 - 10am Central time zone:
well, if you wanted this to be "dynamic" - you would have to do something like:
ops$tkyte%ORA10GR2> create table t
2 ( x number(5),
3 y number(10),
4 z number(15)
5 )
6 /
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1,2,3 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select to_char( x, 'fm'||rpad('0',(select data_precision
2 from user_tab_columns
3 where table_name = 'T'
4 and column_name = 'X'),'0') ) x,
5 to_char( y, 'fm'||rpad('0',(select data_precision
6 from user_tab_columns
7 where table_name = 'T'
8 and column_name = 'Y'),'0') ) y,
9 to_char( z, 'fm'||rpad('0',(select data_precision
10 from user_tab_columns
11 where table_name = 'T'
12 and column_name = 'Z'),'0') ) z
13 from t
14 /
X Y Z
----- ---------- ---------------
00001 0000000002 000000000000003
but I would strongly recommend a VIEW here instead - you don't want to do unnecessary work and those scalar subqueries epitomize unnecessary work if you ask me - we can just maintain the view as part of our application if the lengths change in the future:
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select to_char( x, 'fm' || rpad('0', 5,'0') ) x,
4 to_char( y, 'fm' || rpad('0',10,'0') ) y,
5 to_char( z, 'fm' || rpad('0',15,'0') ) z
6 from t
7 /
View created.
ops$tkyte%ORA10GR2> select * from v;
X Y Z
----- ---------- ---------------
00001 0000000002 000000000000003
Setting Line size
March 22, 2008 - 3pm Central time zone
Reviewer: sriram
Hi Tom ,
Wishing you and your family a happy Easter
We are running a sql script and spool the output. We are hardcoding LINESIZE ( 600 here ),
PAGESIZE etc here in th beginning of the script. Since it is hardcoded it is unnecessarily creating
lot of spaces in the csv files it generates. One way is to count the size of all the coumns used in
the sql scripts and then give exactly the same linesize. But it will be an issue later point if we
add new columns or so. Nobobdy will notice the linesize that time.
Since the csv files is consuming lot of spaces and extra lines we have to run another script in
unix to replace that, before loading those datas in our database. This impacts our perforamce
heavily. Is there any way we can set this linesize automatically. i.e, it automatically shrinks or
expands depends on the size of the data it retrives?.
Sample format
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 600
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF
spool ../../csv/data.csv
< SELECT STATEMENTS >
spool off
exit
Thanks
Followup March 24, 2008 - 11am Central time zone:
set trimspool on
ops$tkyte%ORA10GR2> set linesize 600
ops$tkyte%ORA10GR2> set trimspool off
ops$tkyte%ORA10GR2> spool x
ops$tkyte%ORA10GR2> select * from dual;
D
-
X
ops$tkyte%ORA10GR2> spool off
ops$tkyte%ORA10GR2> !ls -l x.lst
-rw-rw-r-- 1 tkyte tkyte 1875 Mar 24 11:20 x.lst
ops$tkyte%ORA10GR2> set trimspool on
ops$tkyte%ORA10GR2> spool x
ops$tkyte%ORA10GR2> select * from dual;
D
-
X
ops$tkyte%ORA10GR2> spool off
ops$tkyte%ORA10GR2> !ls -l x.lst
-rw-rw-r-- 1 tkyte tkyte 78 Mar 24 11:20 x.lst
fix to calendar
June 6, 2008 - 4pm Central time zone
Reviewer: Chris from Dallas, TX
Tom,
I noticed when I run your calendar code now (in 2008), I get this for December:
December 2008 28 29 30 31
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
With a slight tweak, I got it to display correctly:
Replace all occurrences of to_char(dt+1,'iw') with to_char(dt+1,'iyiw')
December in 2008 then display's correctly:
December 2008 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Good One
August 7, 2008 - 6am Central time zone
Reviewer: Guru from BANGALORE
Hi,
I have a question what does the 'iy' will do in this query.
Thanks,
|