PL/SQL :: How To Get Max Total Sale By Date On Every Store
Jul 6, 2013I am trying to write a query in Oracle which will return both the store_id and the maximum total sale by date
View 3 Replies
ADVERTISEMENT
SQL & PL/SQL :: Report Deign To Generate Month Wise Sale - Date Format
Sep 23, 2013how to write pl/sql to create date o/p like .
since we wnat to design pl/sql report wich will generate monthwise sale from 01jan2009 to 01-sep-2013.
we have created funtion wich will accept argument as employee no, from date and to date.
from_date to_date
01-JAN-2009 01-FEB-2009
01-FEB-2009 01-MAR-2009
01-MAR-2009 01-APR-2009
01-APR-2009 01-MAY-2009
01-may-2009 01-JUN-2009
01-JUN-2009 01-JUL-2009
How To Store Total No Of Updated Rows (number) In A Variable
Jun 21, 2011how to store total no of updated rows (number) in a variable after executing an updation query using script
View 2 Replies View RelatedSQL & PL/SQL :: How To Store Value In Date Variable
Oct 17, 2012i am having one query which is
select to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:ss')from dual;
when i am running this query i am getting this value: 01/01/1900 23:59:00 and this value i am inserting in one variable which has date datatype.here i am storing like this:
v_nshiftmax := to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:SS');
while storing getting error: not a valid month....how to store it in this variable?
SQL & PL/SQL :: Store Date Time From CSV File?
Aug 6, 2013I have a csv file where a field has values like "Fri Aug 2 19:22:02 EDT 2013". through unix i am able to place the value into an unix variable but the problem is this should be inserted into a date column in my database table. Basically, the relevant data to be extracted is "Aug 2 2013 19:22:02" and then i need to insert the same into my database column. And the date column in the table should displays like '08/02/2013 19:22:02'.
create table tst_var(a varchar2(50))
/
create table test_dt(a date)
/
insert into tst_var values('Fri Aug 2 19:22:02 EDT 2013')
/
commit;
SQL & PL/SQL :: To Store The Date And Time In GMT Format
May 13, 2010Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.
Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?
SQL & PL/SQL :: Store The Date In String Type Format
Nov 23, 2010We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
[code].....
SQL & PL/SQL :: Value Of First And Last Sale
Aug 29, 2013I have a sales table where i need to get same indicators about the account, I get the date of first sale and the date of last sale, but I need to get the value of first sale (min date) and the last sale (max date)
WITH My_table AS
(
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130801', 'YYYYMMDD') DTCREATED, 1000 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130815', 'YYYYMMDD') DTCREATED, 400 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130807', 'YYYYMMDD') DTCREATED, 2600 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130808', 'YYYYMMDD') DTCREATED, 500 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130811', 'YYYYMMDD') DTCREATED, 450 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130802', 'YYYYMMDD') DTCREATED, 4000 as Value FROM dual UNION ALL
[Code]....
Where i will get
ID_ACC = 0001
CPD_PRODUCT = ABC
DATE_START = 20130801
DATE_LAST = 20130828
NUM_DAYS = 27
NUM_SALES = 10
And i need this two values
VALUE_FIRST = 1000 (on 20130801)
VALUE_LAST = 10 (on 20130828)
How can i get VALUE_FIRST and VALUE_LAST without using temp tables or sub-select ?
Forms :: Inventory Sale Program In Oracle
Apr 15, 2010I have made the inventory sale program in oracle. Its working from 7 years. Now its speed is very slow.It take much time to execute query.
View 17 Replies View RelatedPL/SQL :: Total And Grand Total
Mar 26, 2013I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.
SELECT decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
--decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
farinva_invh_Code,
spriden_id,
--spriden_last_name "last Name"
[Code]....
SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?
Jun 1, 2010I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date
Apr 12, 2010I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
Store Image And PDF Files In 10g?
Jan 5, 2011i want to store image and pdf files in oracle 10g.
i success with blob variable.
what are the other methods.
SQL & PL/SQL :: Hiding Store Procedure Possible?
Jan 11, 2012I have a question like, Is it possible to hide the Store Procedure?
Scenario: I have write the SP which contains some logic based on my requirement. Once i developed this i need to implement this to my client page. So i need to hide the logic even the client opened the SP like exe file.
SQL & PL/SQL :: How To Store Xml In Blob From A Query
Jul 13, 2012i want to store xml in database. i have following questions,
1) in which col should i keep xml .
2) right now i am keeping it in blob columns, how can i insert update a record in blob col from query, which can be run from worksheet.
I am using oracle 11g express edition.
Forms :: How To Store PDF In Database
Mar 23, 2010I want to store files in the database using oracle forms 6i. The file may be in the form of .pdf .
Its possible, if yes How to code in form.
SQL & PL/SQL :: Script Store Data?
Feb 10, 2013My requirement is to get primary key column name and there values of a table and store in a stage table
create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));
begin
for i in 1..5 loop
insert into easy values(i,'777'||i,i||89,'Ris'||i||i);
[code]...
I was able to fetch primary keys and there values for a table and store them in stage table but only 1 record , I am stuck when there are more than 1 record Here is what i tried
SQL> SET serveroutput ON
SQL> DECLARE
2 V_prim_key VARCHAR2(2000);
3 l_tab DBMS_UTILITY.uncl_array;
[code]...
If you look at the above code in the execute immediate where condition i have used id=1, which is 1 records only but if i change id <3 then there will be 2 records and i will get below error ORA-01422: exact fetch returns more than requested number of rows
Other problem if there are 3 primary keys as in above case i am fetching there values 1 by 1 , is there any way if i can fetch those values once in for all data to be stored in stage table in below format
TAB_NAME V_PEIM_KEY V_PRIM_DATA DT
------- --------- ------- ------
EASY TXN_NAME,EVENT_ROWID,ID Ris11,7771,1 10-Feb-13
EASY TXN_NAME,EVENT_ROWID,ID Ris22,7772,2 10-Feb-13
SQL & PL/SQL :: Right Way To Store IPV4 And MAC Address
Jan 26, 2012The title for this new topic is self explanatory. Since PostgreSQL has data types for storing IPV4 and MAC address and Oracle don't, I would like to know the right way to store this values so we can do different operations and validations with these network address.
I been doing some research but I don't like the solutions like storing just number values whit out any validations like PostgreSQL.
PL/SQL :: Store XML Data In Tables
Jul 21, 2012I Have to write a procedure which takes XML data and inserts into some tables.
If the XML format is fixed then i can use extract function for parsing and can insert into the tables.
But the problem is there is not fixed format for the xml.
Are there any built in packages which takes the xml data for parsing..
SQL & PL/SQL :: How To Store PDF Files In Oracle10g DB
Dec 2, 2011how to store pdf files in oracle10g database
View 13 Replies View RelatedSQL & PL/SQL :: FIFO Store Consumption
Apr 25, 2013I need a query to divide the total consumption of an item into parts as it was stored in store on the basis of FIFO. The item that was stored first it will consumed first.
My sample data is given below.
DROP TABLE STORE_STOCK;
CREATE TABLE STORE_STOCK
(
no NUMBER(4),
vdate DATE,
code VARCHAR2(8),
clqty NUMBER(8,3),
dept_id NUMBER(4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]........
SQL>select * from store_stock
2 order by dept_id,vdate,no;
NO VDATE CODE CLQTY DEPT_ID
---------- --------- -------- ---------- ----------
359 21-JUL-12 012-042C 1050.1 19
2144 14-NOV-12 012-042C 990 19
1876 23-OCT-12 012-042C 1010 34
4006 12-MAR-13 012-043D 515.425 34
4086 15-MAR-13 012-043D 870 34
4495 13-APR-13 012-043D 498 34
6 rows selected.
SQL>select * from store_issue;
CODE IDATE DEPT_ID QTY
-------- --------- ---------- ----------
012-043D 24-APR-13 34 1650
012-042C 24-APR-13 19 990
Required Output will be
NO VDATE CODE CLQTY DEPT_ID ISS_QTY BAL_ISS
---------- --------- -------- ---------- ---------- ---------- ---------
359 21-JUL-12 012-042C 1050.1 19 990 0
2144 14-NOV-12 012-042C 990 19 0 0
1876 23-OCT-12 012-042C 1010 34 1010 640
4006 12-MAR-13 012-043D 515.425 34 515.425 124.575
4086 15-MAR-13 012-043D 870 34 124.575 0
4495 13-APR-13 012-043D 498 34 0 0
The total consumed against code '012-042C' is 990 for dept_id 19 and we have stored qty 1050.1 against goods receipt no 359 dated 12-jul-12. So have issued the whole qty against no 359 and balance is 0. For item code '012-043D' the total issuance is 1650 and we have issued it against 03 goods receipt nos.
SQL & PL/SQL :: Running Total SUM (Amt) Over?
Apr 21, 2012My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.
On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.
Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30
[code]...
My imaginary SQL would look something like at this point (if I have it right):
SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable
How to get the last column AmtLeft?
PL/SQL :: Ranking Sum Total But Not Overall?
Sep 7, 2012I'm trying to Rank Username based on the Total Sum of amount waived but I want to avoid Ranking the Overall Total at the bottom, plus I dont want them in Ranking order, I want the order to stay the same as it currently is.
SELECT DECODE(GROUPING(USERNAME),1,'TOTAL',0,UPPER(USERNAME)) as "USERNAME",
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JAN' THEN AMOUNT_WAIVED ELSE 0 END) AS JAN,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'FEB' THEN AMOUNT_WAIVED ELSE 0 END) AS FEB,
[code]....
Store Procedure Insert / Update?
Dec 7, 2011Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.
View 2 Replies View RelatedTo Store Data In Materialized View
Apr 14, 2011I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.
So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
SQL & PL/SQL :: Store Image In Oracle 9i Table
Jan 3, 2012I want to store Image file in Oracle table without storing it physically in the system. how can i store the image file into the oracle table and also retrieve it without storing it physically in the system at any path.
View 5 Replies View RelatedSQL & PL/SQL :: How To Store Images In Table Column
Dec 21, 2011How to store images in table column. I have table called emp with datatype empno,name,photo_id(long raw)
.
i want to store image in table for each record.
SQL & PL/SQL :: How To Store A Large String In Oracle
Nov 20, 2009how do i store large string in oracle.
View 19 Replies View RelatedSQL & PL/SQL :: Store Large XML Data In Blob?
May 8, 2010Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.
But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?
SQL & PL/SQL :: How To Store Spanish Content In A Table
Jul 29, 2011The requirement is to store "Spanish" content in a table. The input will be passed from the web page. What settings do i need to make in the database to store the Spanish content. Will a varchar2 accept Spanish text ? I need to retrieve back the content and send to web page based on request.
View 6 Replies View Related
