I am writing a query where I'd like to pull one year's worth of data. Ideally I want to prompt for the END DATE and have the query go back in time one year from that date.
Here is what I've got after doing some research online... but It's not quite working for me.
select *
from mrtcustomer.profile
where reg_type = 'B'
and contact_type = 0
and active_ind = 'Y'
[code].....
mucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?
I create the table with RANGE partition just fine:
CREATE TABLE my_part_tab
( id number,
sdate date
)
PARTITION BY RANGE ( sdate )
( PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ),
PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ),
PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
[code]...
Table created.(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition). to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.So far, I have the following working:
ACCEPT lYear PROMPT "Add Paritions for which calendar year?"
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );
[code]...
But no luck ...Same issue with other variations:
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
In the database we use for transfer articulation, there are numerous tables delivered with the product. The institution decided not to use certain fields, and all instances of those fields have no data. In other words, there might be a field in the table called INSTCD, but no records in the table have ever inserted any data into that particular field. In the table there are thousands of records, and we don't necessarily know which of the fields have never been used (no list has been retained and no one who initially was involved in the decisions is available to ask), as there are multiple fields in each table. How can I write a query that pulls only the fields in the table that contain data. In the example below, the SHRTRIT table contains a field called ACTIVITY_DATE, but there is no data in any record in that particular field, so I don't want it to show up on the output. In this particular case, I KNOW not to pull this field in a SELECT, but in a case where there might be 130,000 records and I DON'T know if a field has records in it, how could I do that?
if the question I'm asking doesn't make sense and I'll attempt to word it better.
create table SHRTRIT
(
SBGI_CODE VARCHAR2(6) NOT NULL,
SBGI_DESC VARCHAR2(10),
ACTIVITY_DATE VARCHAR2(10)
)
[Code]....
I'm needing to pull data into a cursor, then split this data into 3 different tables, each having the same number of rows and a select number of columns from the original. i can pull the data, but then i can only access it one row at a time via FETCH, then i can't load into the 3 new CURSORS one row at a time.
View 11 Replies View RelatedI have a staging table and a target table. How do I pull in last loaded data from staging table to target table?
View 4 Replies View RelatedI recently needed a select list that would allow you to choose from a years worth on Mondays starting with the current week's Monday. It will return the date of that Monday. I thought others might be able to use this too... Plus I wanted to be able to find this in the future.
SELECT DISTINCT
to_char(trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw'),'FMMonth DD, YYYY') AS Display
,trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw') as Return
FROM dual
CONNECT BY LEVEL <=((add_months(trunc(SYSDATE,'day'),12)-1)-(trunc(SYSDATE,'day')))
ORDER BY 2;
As of today (Friday, August 24th) the above would return:
DISPLAY RETURN
August 20, 2012 20-AUG-12
August 27, 2012 27-AUG-12
September 3, 2012 03-SEP-12
September 10, 2012 10-SEP-12
September 17, 2012 17-SEP-12
[Code]....
I have a table for exampl,
emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012
i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.
my output like this.....
emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)
My problem is I need to get the data for every quarter for financial year and also I need data for every week for financial year.For example for financial year 2012-13, Apr2012 to Jun2012 would be Q1, Jul2012 to Sep2012 would be Q2 and so on. Total 8quarters should come upto Apr2013.In the same way 1st apr 2012 to 7th apr 2012 would be week1, 8th apr to 15th apr would be week2 and son on. How to write a query for this scenario in oracle.
View 4 Replies View RelatedIs there any way to tell the total data growth increase of some tables when compared with last year.
View 4 Replies View RelatedI have two tables. How I can cast Book Collection_ID number to Book_Name?
Select A.BookCollection_ID from Bookpart A, BookName B where A.BookPart_ID = B.BookPart_ID
OutPut
1,2
Expected OutPut
ToyBook,FunBook
We need to separate 1,2 and extract Book_NAME from Book Part table.
i.e 1 as ToyBook and 2 as FunBook ?
1.CREATE TABLE BookPart (
BookPart_ID INTEGER NOT NULL,
LIMIT_MAX VARCHAR2 (255),
BookCollection_ID INTEGER,
PRIMARY KEY (BookPart_ID )
2.CREATE TABLE BookName (
BookName_ID INTEGER NOT NULL,
BookPart_ID INTEGER,
Business_ID INTEGER,
Book_NAME VARCHAR2 (255),
PRIMARY KEY (BookName_ID )
I want to start by saying I am brand new to SQL. I have an access database linked to my oracle and am trying to query a very specific set of data and I can't seem to narroe it down. I have 244,000 lines in the DB and I'm trying to find items on a specific trype of vendor agreement. I may have the same item on multiple agreements. ex 1-, 1a-, 2-,2a-,3-,3a-,4-,4a-,5-,5a-,6-,6a-,7-,7a-,8-,8a-.
each item by agreement is on it's own line.
ex.
item ven_agrmt_ref
233 1a-xxx
233 4-xxx
233 4a-xxx
255 4a-xxx
I need to find a way to select just items that appear on a 4- or 4a- and no other agreement reference. The query I did so far pulls all of the 4- and 4a- agreements but will also pull items,like #233 in the example above, but not showing the 1a- agreement. I need it to overlook that item eventhough it does appear on the agreement I am looking for but also has an agreement I am not. the statement I am using right now is:
SELECT item, ven_item, ven_agrmt_ref, base_cost, vendor
FROM "all items by agreement"
WHERE ven_agrmt_ref >= '4'
ORDER BY item
I am having trouble trying to pull the next value from a query based on a where clauseThe query I am using is:
Declare nextID NUMBER(22);BEGINselect lag(ref_contact_id, 1,0) OVER (ORDER BY ref_contact_id)into nextID from (select ref_contact_id,lead(ref_contact_id, 1,0) OVER (order BY ref_contact_id) as "NextNbr", lag(ref_contact_id, 1,0) OVER (order BY ref_contact_id) as "LastNbr"from (select rc.ref_contact_id from REF_CONTACT rc order by FIRST_NAME ))where ref_contact_id = 793 ;END; The returned value is 0.
I understand why but not how to pull the next value base on a particular ref_contact_id.
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
View 3 Replies View RelatedI need to pull the 3 newest articles in a news table. Here's a list of rows including dates:
SQL> SELECT newsid, dateadded, ROWNUM from news ORDER BY dateadded DESC;
NEWSID DATEADDED ROWNUM
---------- --------- ----------
61 02-MAY-07 17
47 01-MAY-07 9
46 01-MAY-07 8
45 01-MAY-07 7
44 01-MAY-07 6
43 01-MAY-07 5
42 01-MAY-07 12
41 01-MAY-07 11
[code]....
This seems like such a basic thing to do.
I need to pull most recent status from a table with date field in the case statement.
status date
1 08/28/2013
2 05/12/2-13
3 02/11/2013
I need the status result of 1 (i.e most recent) and have to do in case statement only. Not interested in the date field in the final result.
I want to move data between two instances and recommended we create a local database link to PULL data from remote database located here (supplier on site) but they want to PUSH data to us. I thought you could only PULL data over a database link but then read the link [URL] where PUSH is considered ? I was going to use standard creatas like create table A as select * from table A@<remote_db_link> which works well and fast ( tried and tested) but some are saying they think PUSH quicker/better ?
we do have data "PUSH" already but this does not use a db link - effectively it calls a local proceedure here and passes a row of data and is slow ie for a 1000 row table to be pushed to us we have our local proceedure called 1000 times.
I have always suggested a PULL with db_link as the fastest method - any proof OR info on a fast PUSH method ( that is quicker than PULL ) ? can you REALLY push ?
I'm trying to pull all the degrees into a table based on which institution is selected. If institution is 'AAA' or 'BBB' then pull ACAD_PLAN, DESCR by ACAD_PROG where ACAD_PROG >= some value and <= some other value.
If institution is 'CCC' then pull ACAD_PLAN, DESCR by institution regardless of ACAD_PROG.
Something like
INSERT INTO table
SELECT
'value_a'
[Code].....
I don't have this formatted right cause it keep telling me missing keywords.
SQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.1.0 Production
Forms Version : 6i
Reports Version: 6i
O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3
With regards to the above version description here is my query. I have a form which calls report it accepts various parameters like date between, appeal and so on . I want the report to be restricted to the date parameter as passed by the user.Here is my coding which runs report
Declare
Pl_id ParamList;
where_cond varchar2(2500);
Begin
------------Appeal------------------
if
upper(ltrim(rtrim(:appeal)))<> 'ALL' then
where_cond:= where_cond ||'and tbl_donation.appeal_code='||ltrim(rtrim(:blk_ihelp.appeal_code));
else
where_cond:= where_cond||' and tbl_donation.appeal_code is not null';
end if;
-------------Date Option----------------
if
:date_option is not null then
if
:date_option = 'BETWEEN'then
where_cond:=' and tbl_donation.donation_date between '''||ltrim(rtrim(:fdate))||''' and ''' ||ltrim(rtrim(:tdate))||'''';
else
where_cond:=' and tbl_donation.donation_date '||:date_option||''''||ltrim(rtrim(:fdate))||'''';
end if;
end if;
--------------Country-------------------
if
upper (ltrim(rtrim(:country))) <> 'ALL'then
where_cond:= where_cond||'and tbl_donation.country_code='||ltrim(rtrim(:blk_ihelp.country_code));
else
where_cond:= where_cond||'and tbl_donation.country_code is not null';
end if;
-------------Contact Code---------------
if
:contact_code is not null then
if
:contact_code = 'BETWEEN'then
[Code]....
I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number.
sample:
select substr(spriden_last_name,1,20)||', '||
substr(spriden_first_name,1,20)||' '||
substr(spriden_mi,1,1) stname,
'<img src = "/images/&1..JPG" width="400" height="400"/>' pic
from spriden
where spriden_id = '&1'
/
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
What is the function to prompt a user for a value when running a script and where would it be used?
I was also wondering if it is possible to populate a list from a table that would be displayed in a drop down box for the user to choose from?
I have a procedure with signature as proc_temp(deptno in number, empdetails out sys_refcursor);
how can i call this procedure from sql prompt.
I decided to uninstall oracle 10g, and APEX 4.0.
I then reinstalled oracle 10g express and downloaded APEX 3.2..
However, wen its time to upgrade to APEX3.2 via the command prompt... the previous version of APEX 4.0 (11.1.0.6.0) is being read.
how to reset this to the original value 10.2.0.10 (10g express edition) ?
I have created a text prompt in layout editor by clicking a button 'TEXT'('A' symbol) from left hand side toolbar,text is created with name 'TEXT67' and graphics type 'TEXT'.
when i click that text(TEXT67),block showing <null>.
So i cant use the below code,bcoz no block for that text..
SET_ITEM_PROPERTY('<BLOCK_NAME>.<TEXT_NAME>',PROMPT_TEXT,' ');
then i tried below,i.e i skip block name i gave only text name..
SET_ITEM_PROPERTY('TEXT67',PROMPT_TEXT,' ');
not working...
how to run dos command from sql prompt.
i.e.
SQL>!DIR
Same can be done in unix using ! character.
SQL>!ls -lrt
Whenever I deploy a PSU patch I receive the prompt below. I'm attempting to deploy PSU in an automated fashion. How do I suppress the prompt? "Enter 'yes' if you have unzipped this patch to an empty directory to proceed (yes/no):"
View 0 Replies View Relatedi want a procedure or any other way to change the prompt of a form with another nameand get the name from a table in database that table contain the old name ,, and the new name
i want to use this procedure to facilitate different language user to change the prompt of all item to the user language i have done one but it's not as good as i want i need it to work with a big sys with a lot of items and change all it;s prompt
all i have to do is to fill the table with the words only
Text field prompt displays ? while using bengali language but text field displays value in bengali language when retrieve data from database.
I am using oracle 11g database in windows platform and oracle 10g application server release 2 in linux(rhel 4) platform. For client machine I am using windows xp and jre version 6 update 14
To do this I add following line into user bash profile in application server:
export NLS_LANG=AMERICAN_AMERICA.UTF8
I also edit oracle_home/forms/java/oracle/forms/registry/Registry.dat file as below:
...
....
#default.fontMap.defaultFontname=Dialog
default.fontMap.defaultFontname=SolaimanLipi
I am trying to get the TNS_ADMIN value but I m an not getting Expected Results . My tnsNames.Ora are located at the following locations : -
1) C:oracleproduct10.2.0client_1NETWORKADMIN
2) C:oraclexeapporacleproduct11.2.0server
NetworkADMIN
As you can see from the belowcode, TNS_ADMIN is not displaying the correct location.
C:>set TNS_ADMIN=%ORACLE_HOME%
etworkadmin
C:>echo %TNS_ADMIN%
%ORACLE_HOME%
etworkadmin
C:>echo %ORACLE_SID%
%ORACLE_SID%
C:>
I am able to start SQLPLUS from Start | Program files | Oracle - OraHome92 | Application development |
Unfortunately, when trying to start it from the "command prompt" window with the following command SQLPLUS, it generates the infamous error message like
"... unknown etc... "
Any variables i need to change on my client machine ?
I'm trying to hide the password for the batch programs that connect to the DB Server
as Cadot pointed out in
[URL].........
Quote:
use secure external password store
with reference to
[URL].........
when I create wallet, the system does not prompt me for password
C:>mkstore -wrl "C:ora102NETWORKADMIN" -create
when creating login credentials, again the system never prompts me for password
C:>mkstore -wrl "C:ora102NETWORKADMIN" -createCredential db10g scott tiger
here's my sqlnet.ora configurations
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY =C:ora102NETWORKADMIN)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0
here's my tnsname.ora settings
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mike)
)
)
here's the outcome
C:Documents and SettingsAdministrator>sqlplus /@db10g
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 13 22:53:06 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12534: TNS:operation not supported
Enter user-name:
so I Google around for the solution to the ORA-12534 error, one of the site,
[URL].......
here's my lsnrctl services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "MIKEXDB" has 1 instance(s).
Instance "mike", status READY, has 1 handler(s) for this service...
Handler(s):
[code].....
The command completed successfully
right now I think I will be a fool to think that the solution is to resolve the ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. so what is wrong with my setup, or is it some patch that I need to apply?