This content has been marked as final. Show 3 replies
1 person found this helpful
Edited by: ranit B on Nov 22, 2012 12:54 AM
insert into temp_quarter_vj select to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q')||level as "Qnum_in_year", level "year", to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q') "Qnum" from dual where level>=2006 connect by level <= 2030;
choti wrote:That's thinking ahead! With 128 characters for qnum_in_quarter, you'll be all set through the year 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999. But why do you need 128 characters for the quarter? Given that you do need 128 characters for qnum, shouldn't you have at least 132 characters for qnum_in_year?
Please help me to insert data into the following table
create table xddw.temp_quarter_vj (QNum_in_Year varchar2(128) ,Year Number ,QNum varchar2(128) )
If you don't need that many characters, make the maximum sizes smaller. Making the strings to big is just asking for trouble.
Here's one way:
Insert data into table from 2006 to 2030 Expected output for 01.01.2006 QNum_in_Year=012006 year=2006 Qnum=01
INSERT INTO temp_quarter_vj (qnum_in_year, year, qnum) WITH quarters AS ( SELECT TO_CHAR (LEVEL, 'FM00') AS qnum FROM dual CONNECT BY LEVEL <= 4 ) , years AS ( SELECT 2005 + LEVEL AS year FROM dual CONNECT BY LEVEL <= 25 ) SELECT q.qnum || TO_CHAR (y.year) , y.year , q.qnum FROM quarters q CROSS JOIN years y ;
create table xddw.temp_quarter_vj
Why are you using a table like this that contains redundant data and has numeric data stored as strings?
Why not just create a view that gives you the information you need in the format you want?