This content has been marked as final. Show 3 replies
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 ;