creating sql query for producing result as per below from dual table.
SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc
from above query the result is
NOV-2011
but i need the result as per below
APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;
[code]...
I tried to find out recently updated table list. I couldn't find anything.
CODE>> JUST SAMPLE TRANSACTIONS >>
SQL> conn sam
Enter password:
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$y1TXhcjGApvgQAB/AQBOpA==$0 TABLE
TAB1 TABLE
[code]........
i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"
i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"
another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table
why the dual table has only column?
View 3 Replies View RelatedWhat are all the DML operation can be done in DUAL table.?
View 5 Replies View RelatedCan we perform DDL & DML Commands on dual table????
If yes then how??
Should the following code work? I feel like I have done this before, but I'm getting a "must be a subquery" error now.
update TABLE1
set (FIELD1, FIELD2) =
values(select 'blah','blah' from DUAL)
where PK_FIELD=12345;
If this is invalid, is there another way to hardcode a series of values like this?
I have the following code which will produce a list of dates -
SELECT to_date('01-Jan-2013','DD-Mon-YYYY') + rownum - 1
FROM all_objectsWHERE rownum <= to_date('31-Jan-2013','DD-Mon-YYYY') - to_date('01-Jan-2013','DD-Mon-YYYY') + 1 01/01/201302/01/201303/01/201304/01/2013.....30/01/201331/01/2013
But what I'm looking for is a way to also include all possible hours with the dates, so my output would be like -
01/01/2013 0001/01/2013 0101/01/2013 0201/01/2013 03...31/01/2013 2131/01/2013 2231/01/2013 23
Oracle Developer Suite 10g
I want to create List item with time .
like in pic when I click on upper side at list item increase time and other side decrease time.
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
I do need to mantain a table with the las 13 of a month, for example:
ID Month_ID MONTH_MAT
1 201206 201106
2 201206 201107
3 201206 201108
4 201206 201109
5 201206 201110
[Code]....
Kind of a year-to-date table but this one is 13 months one.
I have been trying to create the code from scratch
I have thought of using a Function:
Query: SELECT TRUNC(ADD_MONTHS(SYSDATE, -13),'MM') FROM dual;
Result: 01-MAY-11
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
View 2 Replies View RelatedI've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..
select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual
[Code]...
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH
The part in bold is what I'm having trouble figuring out.
select nvl(
(select 'MATCH'
from dual
where 'date' = '2 week intervals starting Jan 01'
), 'NO_MATCH') from dual
when i run this nls qusery i got this error
E:oracleproduct10.2.0db_1BIN>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
DD-MON-RR
SQL> select sysdate from dual;
SYSDATE
----------------
30-????? -12
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /
#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedI have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)
But it does not raise any exception and the query works absolutely fine generating desired results when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)
I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.
I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.
However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.
PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.
OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
[code]...
I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.
--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);
-- Option1 - Using sequence.NEXTVAL from DUAL
DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;
-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause
DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
View 4 Replies View RelatedWe are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2 . It throws error unknown referenced column error. Command> select current_date from dual; 2211:
Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support.
TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function
Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
how to i insert a constraint of words into the table example below.I am new to this stuff man.
Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);
The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.
How am I suppose to enter this constraint value into the creation of this table?
I have a directory defined as TDM_IN where a directory has been created. There is a table which has been created as
create table abc.IFP_GRP_D_INT
(
IFP_GRP_DIM_KEY VARCHAR2(10),
.....
[Code]...
Now how can i select the records from this table?
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;
/
ALTER TRIGGER "P_M_YES" ENABLE
/
I get an invalid trigger specification.