SQL & PL/SQL :: Updating A Date Field With Field From Another Table?

Nov 14, 2011

I have a table called Customer_Type with following fields

Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011

Tbale 2:Customer

Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly

What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql

I have teh code using merge function..I want something in traditional old fashion..

The sql statements are below

CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),

[Code]....

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Count Of Date Difference Using Pending State Value In Table Field

Feb 2, 2012

Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.

Example as needed:
NoPendingDays = 23 (8+15)

I have attached Create table and Insert table values sample as SQL file.

View 10 Replies View Related

Updating Field Based On Two Tables

Mar 19, 2007

I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.

ORA-00933: SQL command not properly ended

The sql I am using is as follows:

update PS_TRNS_CRSE_DTL
set RQMNT_DESIGNTN = 'TRN'
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
[code]......

View 6 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

Forms :: Date Field Gets Disabled When Date Format Is Given?

Oct 3, 2012

A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..

I am getting this error while trying to update the record

FRM-40509 :Oracle error :unable to update record

I have kept the enabled = yes
required=no
data type=Date.. in the property pallet

View 2 Replies View Related

Server Utilities :: Field In Datafile Exceeds Maximum Length For Number Field?

Apr 23, 2010

Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.

View 13 Replies View Related

Forms :: Update Field Based On Another Field Checkbox

May 3, 2013

I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.

I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.

--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);

--i want to set the value of trans_Act_qty as trans_qty

--i create one dummy or test block to keep the select all check box. for that table test script is

CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);

insert into test (C,B,A) values ('A',1,1);

--code written in select all check box which is created on test.block.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......

--code written in M_END_YN ( actual check boxes where i will uncheck).

IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;

--code written on button to update those values which are checked.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......

View 5 Replies View Related

How To Enable And Disable A Field Dependent On Another Field In Apex

May 9, 2013

I am New to Apex. Im using Apex Version 4.2.

I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.

The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .

But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User

The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*

If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.

View 2 Replies View Related

SQL & PL/SQL :: Getting Date Gaps From Data Of Date Field

Sep 24, 2011

I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.

i.e. :

CREATE TABLE TRY_F (DAT DATE);

INSERT ALL
INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY'))
SELECT * FROM DUAL;

I need a smart way of getting the dates that were missed in DAT.

View 4 Replies View Related

PL/SQL :: Entire Date / Time Value In A Date Field

May 21, 2013

I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work

select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;

However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?

Oracle DB: 11.2.0.3.0

View 3 Replies View Related

Update Field With 1st 9 Characters Of Another Field In Same Record?

Apr 23, 2009

I need to update a field with the 1st 9 characters of another field in the same record.

View 1 Replies View Related

Convert DateTime Field To Date

Jun 11, 2010

I want to force a datetime field to display as date only. How can I do this? This is so when prompting for a value for this field a user doesn't have to also enter the time. At the moment the prompt returns nothing when entering only a date as it does not match any value as they all have times also.

View 12 Replies View Related

SQL & PL/SQL :: Year To Date Value In Amount Field

Oct 18, 2010

I have a table and below are the rows:

create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)

Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;

I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.

The output should be:

Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200

I guess this should be achievable using some analytical functions, but I am unable to get the desired output.

View 7 Replies View Related

SQL & PL/SQL :: Getting Date Portion Of Timestamp Field?

May 19, 2011

I have a timestamp field in an oracle table. The data in that field looks like this.

19-MAY-2011 10.55.21.628206000 AM

I want to query the data in this field by only date portion (not the time portion).

Something like this.

Select * from mytable where archivedate = to_date('19-may-2011','dd-mon-yyyy')

this query doesn't return any data. But actually there is data for 19-may-2011 (along with time portion) in that field.

how to query based on only date portion?

View 4 Replies View Related

SQL & PL/SQL :: Return A Field In Date Format

Mar 23, 2011

I'm trying to return a field in a date format, but my minutes keep returning as 03. See example below:

TO_CHAR (i.editdate, 'mm/dd/yyyy HH24:MM:SS') AS "Date",

Actual data field in the table contains:
10/27/2010 1:07:42 PM

Data returned is:
10/27/2010 13:03:42

Why are the minutes incorrect?

View 10 Replies View Related

SQL & PL/SQL :: Concatenating Date And Time Field

Jan 25, 2012

I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.

I tried the following below but I keep having problems.

select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx

View 5 Replies View Related

Forms :: Date Field With Time

Feb 16, 2010

I have a display item form field. The field's data type is date. It is also a database field that corresponds to a date field in the table.

I have set the format mask on the form field to
MM/DD/YYYY HH24:MI:SS

I can populate that field with sysdate in when-new-form-instance.

However, if I try to get the date and the time in the field, it just populates the date with the time of 00:00:00.
(02/16/2010 00:00:00 for example).

I have tried several to_char and to_date variations but can't seem to get the current time into that field.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Hours / Minutes And Second From Date Datatype Field

Jan 14, 2012

I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :

SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;

VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00

ALTHOUGH THERE IS TIME . Its not zero .

View 3 Replies View Related

Reports & Discoverer :: Current Date Field

Sep 13, 2010

if i make the source for a field is a current date with format dd/mm/rrrr hh:mi:ss and the report published on the application server and the clients from different machines run this report

what the field display on Cline or on DB or On Application Server?

View 3 Replies View Related

List Count Of Rows Where DATE Field Is Not Null?

Apr 1, 2010

I need to list a count of rows where a DATE field is not null and group those counts by day.

Here's my sql so far...

SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;

Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?

View 2 Replies View Related

Application Express :: Default Value Date Picker Field

Nov 21, 2012

In a detail tabular form I am referencing a date picker field from the master form as default value.This is the situation in the detail form:

TABULAR FORM
Column Name: FECHAREGISTRO -- the field's name in the detail tabular form

Default Type: Item (application or page item name)
Default: P68_FECHAREGISTRO -- It's a field in the master form and also is Date Picker format
Reference Table Owner: SAMPEDRORIVEROS
Reference Table Name: BITACORAABOGADO
Reference Column Name: FECHAREGISTRO -- It's a date field in the database

At execution time I have the error:

report error:ORA-01790: expression must have same datatype as corresponding expression

I think it is because i must use to_date and to_char in order to change the datatype and i have tried using:

Default: to_date(to_char(:P68_FECHAREGISTRO,'DD-MON-YYYY'),'DD-MON-YYYY')

and in Default Type: PL/SQL Expression or Function but I get a different message at execution time:

report error: ORA-01722: invalid number.

View 1 Replies View Related

Forms :: How To Make A Date Field Filter In Descending Order

Aug 5, 2011

I have a question regarding a Date field on one of my form.

How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?

View 2 Replies View Related

Forms :: Unable To Get Records While Queried Through Date Field In Oracle

Oct 16, 2012

In a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records.

View 10 Replies View Related

Server Utilities :: How To Load Date Field Along With Timestamp Using Sqlldr

Apr 17, 2013

I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format

100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM

how to load the date filed along with the time stamp.

View 2 Replies View Related

SQL & PL/SQL :: Build Parametric Queries - Input Date Field At Run-time

Aug 24, 2012

When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle

Example

SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=[Enter Date];

View 15 Replies View Related

Application Express :: Disable Entering Text In Date Field

Oct 23, 2012

I am using APEX 4.2

I have created a form with a Date From and Date To fields.

I am using the Date Picker format (the new one, not the Classic), and have set the calendar to Show on Both (Focus and Icon Click).

I would like to restrict the field so that the user cannot enter any text - they can only use the Date picker/calendar to choose a date. This would them remove the clunky validations - so I can restrict the users so they can't enter the wrong format in the first place.

Is this possible?

View 4 Replies View Related

Forms :: How To Pass Excel General Value To Database Tables Date Field

Jun 17, 2013

i've following code

declare
i INTEGER:=0;
flag boolean:= FALSE;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;

[code]....

i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.

if i put this condition
vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
i get the error
to_char must be declare.

if i remove this line

i am getting this error ORA-01843: not a valid month

View 8 Replies View Related

Application Express :: 4.2 - How To Update Field / Column With Sysdate Server Date

Mar 6, 2013

Environment:
Apex 4.2
DB 11gR2

I've started using Apex 4.2(new to Apex). I have built a form based on a table with a date column on it. The form allows update on all fields/columns of the base table. For the date field/column, the goal is to present the user with sysdate on load and update the date with sysdate if the record is changed any of the fields. How can I accomplish this?

View 7 Replies View Related

SQL & PL/SQL :: Update Table Field From Another?

Mar 28, 2011

I'm trying to update a field from another field in the same table.

I got a Table with 3 fields.

CREATE TABLE CONTENT
( LID_id NUMBER(6,0) NOT NULL ENABLE,
LPRECIS VARCHAR2(4000 CHAR),
LCOMMENT VARCHAR2(4000 CHAR)
)

How to update "LPRECIS" from "lCOMMENT" only where "LPRECIS" is NULL ?I used this statement but it's wrong.

UPDATE CONTENT
SET LPRECIS=(SELECT LCOMMENT
FROM CONTENT
WHERE LPRECIS IS NULL)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved