14 thoughts on “Oracle Question and Answer Site”

  1. 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

  2. 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.

  3. 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!

  4. 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

  5. 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

  6. 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.

  7. 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

  8. Dear John,

    Run the following commands at the SQL prompt:

    Break on c1
    select * from temp order by c1;

    Hope it helps.
    Rgds,
    Srin

Leave a Reply

Your email address will not be published. Required fields are marked *