Tom Kyte points out a new Oracle Q&A site oraqa.com. The site is young, but very active and covers many of the topics of interest to Oracle DBAs and developers.
I would like to ask how shall I do a sql script or a function which returns me the calendar week in a month .exemple
my week starts on sunday and ends on sturday .
saturday 1/2005 => 1
sundayday 2/2005 => 2
monday 3/2005 => 2
tuesday 4/2005 => 2
wednesday 5/2005 => 2
thursday 6/2005 => 2
friday 7/2005 => 2
Saturday 8/2005 => 2
Sunday 9/2005 => 3 –shifted
monday 10/2005 => 3
tuesday 11/2005 => 3
wednesday 12/2005 => 3
thursday 13/2005 => 3
friday 14/2005 => 3
Saturday 15/2005 => 3
Sunday 16/2005 => 4 –shifted
monday 17/2005 => 4
tuesday 18/2005 => 4
wednesday 19/2005 => 4
thursday 20/2005 => 4
friday 21/2005 => 4
Saturday 22/2005 => 4
Sunday 23/2005 => 5 –shifted
and so one for the following month february again same thing has to happen starting by 1 ;
The following is my sql which does not work
select
to_char(to_date(‘2004′,’YYYY’)+rownum-1,’DD-MON-YYYY’) day_full
— ,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-(month_num-1)),’WW’) Week_in_month
— ,Week_number_fiscal_Q is same as Week_number_calendar_Q
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-3*(round((to_number(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’))/3)+.49)-1)),’WW’) Week_number_Q
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-6*(round((to_number(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’))/6)+.49)-1)),’WW’) Week_half_year
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’)-1)),’WW’) Week_in_month
from all_objects where rownum
I had done my testing. I would like to reset my autonumber back to 1. I am using Access interface with oracle as a database.
how to rename a database name
how to rename a database name
ex.current name is ask and modify by scroll
Dharmendra,
I think this is what you’re looking for. BE CAREFUL and never try this on your production system until you have tried it on a test system. The directions below should be considered just a broad guideline and do not cover moving files to another location.
If you need to rename a database the best thing to do is to run the sql command alter database backup controlfile to trace; and grab the resulting sql script from the udump directory.
Grab the SQL from the trace file for the RESETLOGS case and change the line that says CREATE CONTROLFILE REUSE DATABASE "OLDNAME" RESETLOGS ARCHIVELOG
to read CREATE CONTROLFILE SET DATABASE "NEWNAME" RESETLOGS ARCHIVELOG
Now shut down the database and run your SQL script to startup and rename the database. Finally run the following SQL to change the global name of the database.
ALTER DATABASE RENAME GLOBAL_NAME TO NEWNAME;
Don’t forget to update your TNSNames, listener.ora, oratab and any other configuration files on your system.
Hope this helps!
I want to store some temporary data in sql
how to create a temporary table in sql
so that i do condition checks pull out values and store there
select then
i am not able to use views here
I have to select one particular column from a view
it is throwing error for
select registration_id from view_registration;
how can i do that
Burleson describes it here as a technique for improving performance but from what I gather you want to do the same thing.
Hope this helps, and sorry for the slow response.
Hi,
Here is the actual query.
SELECT contract_num_ng,STATE_PERFORM, CITY_PERFORM, COUNTRY_PERFORM,order_num_ng,mod_num_ng,PIID,referenced_PIID,contract_status,
contractor_name,component_code,office_code,date_signed,contact_title,dollars_obligated,
contractor_street,contractor_city,contractor_state,contractor_country,contractor_zip
FROM contract_actions_dw
WHERE date_signed BETWEEN ’01-OCT-2004′ AND ’30-SEP-2006′
AND contract_status=’A’
AND contract_num_ng LIKE ‘HHS%’
AND city_perform IN(
SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name IN(‘FAIRBANKS’,’NORTHPOLE’,’ANCHORAGE’,’GALENA’,’MONTGOMERY’,’TUSCALOOSA’,’TUSKEGEE’,’HUNTSVILLE’,’MOBILE’,’FAYETTEVILLE’,’HOTSPRINGS’,’JONESBORO’,’ELDORADO’,’TUCSON’,’MESA’,’BARSTOW’,’MARINA’,’SEASIDE’,’DEL REY OAKS’,’YUBA CITY’,’NORCO’,’CONCORD’,’SUNNYVALE’,
‘RIVERBANK’,’PORT HUENEME’,’OXNARD’,’RIDGECREST’,’CORONADO’,’SAN DIEGO’,’PASADENA’,’LONG BEACH’,’MOUNTAIN VIEW’,’SAN JOSE’,’COLORADO SPRINGS’,’DENVER’,’MIDDLETOWN’,’WATERBURY’,
‘MILFORD’,’FAIRFIELD’,’WASHINGTON’,’WILMINGTON’,’MILTON’,’PENSACOLA’,’SPRINGFIELD’,’VALPARISO’,’JACKSONVILLE’,’CRAWFORDVILLE’,’KEY WEST’,’FORT WALTON BEACH’,
‘ST PETERSBURG’,’AUGUSTA’,’WARNER ROBBINS’,’ALBANY’,’KINGSLAND’,’HINESVILLE’,’FOREST PARK’,’ATLANTA’,’MARIETTA’,’ATHENS’,’COLUMBUS’,’ROME’,’CEDAR RAPIDS’,’MIDDLETOWN’,
‘DUBUQUE’,’MOUNTAIN HOME’,’SHILOH’,’ROCK ISLAND’,’CENTRALIA’,’FAIRFIELD’,’FOREST PARK’,
‘MARION’,’WAUKEGAN’,’MANHATTAN’,’PARSONS’,’ELIZABETHTOWN’,’PADUCAH’,’LOUISVILLE’,’NEW ORLEANS’,’BOSSIER CITY’,’BATON ROUGE’,’LINCOLN’,’SPRINGFIELD’,
‘CHICOPEE’,’HUGHESVILLE’,’FORT MEADE’,’BETHESDA’,’ABERDEEN’,’ADELPHI’,’KITTERY’,’TOWN OF BRUNSWICK’,’TOPSHAM’,’CHESTERFIELD TOWNSHIP’,’BATTLECREEK’,’CAMBRIDGE’,’FARIBAULT’,’HERMANTOWN’,’KANSAS CITY’,’CAPE GIRARDEAU’,’MERIDIAN’,’PASCAGOULA’,’HELENA’,’MISSOULA’,’HAVELOCK’,’FAYETTEVILLE’,’ALBEMARLE’,’WILMINGTON’,’ASHEVILLE’,’GRAND FORKS’,’LINCOLN’,’PORTSMOUTH’,’EATONTOWN’,’EDISON’,’EWING’,’PENNSAUKEN’,’CLOVIS’,’UNIONDALE’,’AMITYVILLE’,’NEW YORK CITY’)
AND s.state_code IN (‘GA’,’IA’,’ID’,’IL’,’IN’,’KS’,’KY’,’LA’,’MA’,’MD’,’ME’,
‘MI’,’MN’,’MO’,’MS’,’MT’,’NC’,’ND’,’NE’,’NH’,’NJ’,’NM’,
‘NY’,’OH’,’OK’,’OR’,’PA’,’PR’,’RI’,’SC’,’SD’,’TX’,’VA’,’VT’,’WA’,’WI’,’WV’)
AND c.state_num=s.state_num
GROUP BY c.city_code,s.state_code
Now the problem is I have a list of companies which exists only in one particular city in a particular state. But my query gives multiple columns for a city matching with many states. But I want only the state and city where the company exists. The company info does not exist in any table in dbase. Just by matching the city and state i have to select the compnany info. Pls advice.
I hv migrate forms 6i into 10 and it gives the error vbx.set_property .
what is alternate .
i have table
c1 c2
———-
1 a
2 b
1 c
1 d
2 e
i want a query forthis table which will dispaly it as
Tom Kyte points out a new Oracle Q&A site oraqa.com. The site is young, but very active and covers many of the topics of interest to Oracle DBAs and developers.
I would like to ask how shall I do a sql script or a function which returns me the calendar week in a month .exemple
my week starts on sunday and ends on sturday .
saturday 1/2005 => 1
sundayday 2/2005 => 2
monday 3/2005 => 2
tuesday 4/2005 => 2
wednesday 5/2005 => 2
thursday 6/2005 => 2
friday 7/2005 => 2
Saturday 8/2005 => 2
Sunday 9/2005 => 3 –shifted
monday 10/2005 => 3
tuesday 11/2005 => 3
wednesday 12/2005 => 3
thursday 13/2005 => 3
friday 14/2005 => 3
Saturday 15/2005 => 3
Sunday 16/2005 => 4 –shifted
monday 17/2005 => 4
tuesday 18/2005 => 4
wednesday 19/2005 => 4
thursday 20/2005 => 4
friday 21/2005 => 4
Saturday 22/2005 => 4
Sunday 23/2005 => 5 –shifted
and so one for the following month february again same thing has to happen starting by 1 ;
The following is my sql which does not work
select
to_char(to_date(‘2004′,’YYYY’)+rownum-1,’DD-MON-YYYY’) day_full
— ,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-(month_num-1)),’WW’) Week_in_month
— ,Week_number_fiscal_Q is same as Week_number_calendar_Q
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-3*(round((to_number(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’))/3)+.49)-1)),’WW’) Week_number_Q
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-6*(round((to_number(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’))/6)+.49)-1)),’WW’) Week_half_year
,to_char(add_months(to_date(‘2004′,’YYYY’)+rownum-1,-(to_char(to_date(‘2004′,’YYYY’)+rownum-1,’MM’)-1)),’WW’) Week_in_month
from all_objects where rownum
Ajama, I think I’ve got a solution for you. Check out my article SQL: Find the Week Number in a Month.
Thanks for the challenge!
I had done my testing. I would like to reset my autonumber back to 1. I am using Access interface with oracle as a database.
how to rename a database name
how to rename a database name
ex.current name is ask and modify by scroll
Dharmendra,
I think this is what you’re looking for. BE CAREFUL and never try this on your production system until you have tried it on a test system. The directions below should be considered just a broad guideline and do not cover moving files to another location.
If you need to rename a database the best thing to do is to run the sql command
alter database backup controlfile to trace;and grab the resulting sql script from the udump directory.Grab the SQL from the trace file for the RESETLOGS case and change the line that says
CREATE CONTROLFILE REUSE DATABASE "OLDNAME" RESETLOGS ARCHIVELOGto read
CREATE CONTROLFILE SET DATABASE "NEWNAME" RESETLOGS ARCHIVELOGNow shut down the database and run your SQL script to startup and rename the database. Finally run the following SQL to change the global name of the database.
ALTER DATABASE RENAME GLOBAL_NAME TO NEWNAME;Don’t forget to update your TNSNames, listener.ora, oratab and any other configuration files on your system.
Hope this helps!
I want to store some temporary data in sql
how to create a temporary table in sql
so that i do condition checks pull out values and store there
select then
i am not able to use views here
I have to select one particular column from a view
it is throwing error for
select registration_id from view_registration;
how can i do that
Pradeep,
I think you’re looking for something like this:
http://www.dba-oracle.com/t_temporary_tables_sql.htm
Burleson describes it here as a technique for improving performance but from what I gather you want to do the same thing.
Hope this helps, and sorry for the slow response.
Hi,
Here is the actual query.
SELECT contract_num_ng,STATE_PERFORM, CITY_PERFORM, COUNTRY_PERFORM,order_num_ng,mod_num_ng,PIID,referenced_PIID,contract_status,
contractor_name,component_code,office_code,date_signed,contact_title,dollars_obligated,
contractor_street,contractor_city,contractor_state,contractor_country,contractor_zip
FROM contract_actions_dw
WHERE date_signed BETWEEN ’01-OCT-2004′ AND ’30-SEP-2006′
AND contract_status=’A’
AND contract_num_ng LIKE ‘HHS%’
AND city_perform IN(
SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name IN(‘FAIRBANKS’,’NORTHPOLE’,’ANCHORAGE’,’GALENA’,’MONTGOMERY’,’TUSCALOOSA’,’TUSKEGEE’,’HUNTSVILLE’,’MOBILE’,’FAYETTEVILLE’,’HOTSPRINGS’,’JONESBORO’,’ELDORADO’,’TUCSON’,’MESA’,’BARSTOW’,’MARINA’,’SEASIDE’,’DEL REY OAKS’,’YUBA CITY’,’NORCO’,’CONCORD’,’SUNNYVALE’,
‘RIVERBANK’,’PORT HUENEME’,’OXNARD’,’RIDGECREST’,’CORONADO’,’SAN DIEGO’,’PASADENA’,’LONG BEACH’,’MOUNTAIN VIEW’,’SAN JOSE’,’COLORADO SPRINGS’,’DENVER’,’MIDDLETOWN’,’WATERBURY’,
‘MILFORD’,’FAIRFIELD’,’WASHINGTON’,’WILMINGTON’,’MILTON’,’PENSACOLA’,’SPRINGFIELD’,’VALPARISO’,’JACKSONVILLE’,’CRAWFORDVILLE’,’KEY WEST’,’FORT WALTON BEACH’,
‘ST PETERSBURG’,’AUGUSTA’,’WARNER ROBBINS’,’ALBANY’,’KINGSLAND’,’HINESVILLE’,’FOREST PARK’,’ATLANTA’,’MARIETTA’,’ATHENS’,’COLUMBUS’,’ROME’,’CEDAR RAPIDS’,’MIDDLETOWN’,
‘DUBUQUE’,’MOUNTAIN HOME’,’SHILOH’,’ROCK ISLAND’,’CENTRALIA’,’FAIRFIELD’,’FOREST PARK’,
‘MARION’,’WAUKEGAN’,’MANHATTAN’,’PARSONS’,’ELIZABETHTOWN’,’PADUCAH’,’LOUISVILLE’,’NEW ORLEANS’,’BOSSIER CITY’,’BATON ROUGE’,’LINCOLN’,’SPRINGFIELD’,
‘CHICOPEE’,’HUGHESVILLE’,’FORT MEADE’,’BETHESDA’,’ABERDEEN’,’ADELPHI’,’KITTERY’,’TOWN OF BRUNSWICK’,’TOPSHAM’,’CHESTERFIELD TOWNSHIP’,’BATTLECREEK’,’CAMBRIDGE’,’FARIBAULT’,’HERMANTOWN’,’KANSAS CITY’,’CAPE GIRARDEAU’,’MERIDIAN’,’PASCAGOULA’,’HELENA’,’MISSOULA’,’HAVELOCK’,’FAYETTEVILLE’,’ALBEMARLE’,’WILMINGTON’,’ASHEVILLE’,’GRAND FORKS’,’LINCOLN’,’PORTSMOUTH’,’EATONTOWN’,’EDISON’,’EWING’,’PENNSAUKEN’,’CLOVIS’,’UNIONDALE’,’AMITYVILLE’,’NEW YORK CITY’)
AND s.state_code IN (‘GA’,’IA’,’ID’,’IL’,’IN’,’KS’,’KY’,’LA’,’MA’,’MD’,’ME’,
‘MI’,’MN’,’MO’,’MS’,’MT’,’NC’,’ND’,’NE’,’NH’,’NJ’,’NM’,
‘NY’,’OH’,’OK’,’OR’,’PA’,’PR’,’RI’,’SC’,’SD’,’TX’,’VA’,’VT’,’WA’,’WI’,’WV’)
AND c.state_num=s.state_num
GROUP BY c.city_code,s.state_code
Now the problem is I have a list of companies which exists only in one particular city in a particular state. But my query gives multiple columns for a city matching with many states. But I want only the state and city where the company exists. The company info does not exist in any table in dbase. Just by matching the city and state i have to select the compnany info. Pls advice.
I hv migrate forms 6i into 10 and it gives the error vbx.set_property .
what is alternate .
i have table
c1 c2
———-
1 a
2 b
1 c
1 d
2 e
i want a query forthis table which will dispaly it as
c1 c2
————
1 a
– c
– d
2 b
– e
– means blank space
Dear John,
Run the following commands at the SQL prompt:
Break on c1
select * from temp order by c1;
Hope it helps.
Rgds,
Srin