SQL & PL/SQL :: Field From Two Tables?

Sep 10, 2012

I have two master tables , one is supplier master and the other customer, the requirement is , i will pass a code as IN parameter and the result it should bring the appropriate name of that code , for example if i pass a code which exists in supplier master , it should get me the supplier name for that code , if i pass code which exists in customer ,it should bring customer name.

CREATE TABLE OM_CUST (CUST_CODE VARCHAR2(30),CUST_NAME VARCHAR2(240));
INSERT INTO OM_CUST VALUES ('1001','CUST1');
INSERT INTO OM_CUST VALUES ('1002','CUST2');
CREATE TABLE OM_SUPP (SUPP_CODE VARCHAR2(30),SUPP_NAME VARCHAR2(240));
INSERT INTO OM_SUPP VALUES ('2001','SUPP1');
INSERT INTO OM_SUPP VALUES ('2002','SUPP2');

View 6 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Linking Two Tables With Extra Field

Oct 1, 2011

Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.

SQL> SELECT count(*)
2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR
3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER;
COUNT(*)
----------
2243
[code]...

View 3 Replies View Related

Omit Results - Two Tables That Each Have A Status Field?

Aug 6, 2010

I'm trying to leave out results of a query that match certain conditions, basically I'm looking at two tables that each have a status field. If the status of both is equal to zero then I want the result to be left out of the search results. I'm struggling with the logic though. In the where clause I've tried:

(ACCO.STATUS != '0' AND CCO.STATUS != '0')

this, however, omits everything where the status combination isn't 1 and 1. I've also tried:
(
(ACCO.STATUS = '1' AND CCO.STATUS = '1') OR
(ACCO.STATUS = '1' AND CCO.STATUS = '0') OR
(ACCO.STATUS = '0' AND CCO.STATUS = '1')
)

but this does not work either.

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

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

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

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 View Related

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

View 2 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

SQL & PL/SQL :: Audit Tables For Most Of Production Tables

May 29, 2013

In our schema we have corresponding audit tables for most of the production tables

Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU

Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify

1) Production tables where corresponding audit table is missing

2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table

View 11 Replies View Related

PL/SQL :: Same Value Until Field Changes

Jan 24, 2013

I have bills and payments for a customer, a customer may make many payments against 1 bill. I want to figure out the time taken from bill issue date to the time the balance of the customer falls to less then 10% of the most recent bill.I have managed to compute the running balance for the customer

So something like:
DATE      AMT      BILL_AMT     BILL_DATE     BALANCE
6-Oct-11     38533 38533     6-Oct-11 38533
20-Sep-12     -6000           0
6-Dec-11     78552 78552     6-Dec-11 78552
[code]....

But I need to know when the running balance falls beneath 10% of the previous bill.

I thought a way to do this would be to keep the same bill amount in the BILL_AMT column until the BILL_AMT is not null which means that a new bill has been issued.

I can't use LAG because the number of records I am going back by increases by 1 for each row where the BILL_AMT is null.I also can't use a running total because I don't want the total for all of the records for the customer, just the total (i.e. the bill amount and all intervening nulls (which would be the bill amount)) since the last bill?

i.e. how can I keep the same value in a field as long as the values in that field are null and then adopt a new value when there is a non-null value?

View 2 Replies View Related

SQL & PL/SQL :: Delete One Particular Field Value In A Row?

Feb 22, 2010

How to delete one particular field value in a row?

Constraint: There should be no null value or zero instead of deleted value. Other values remain unchanged.

View 14 Replies View Related

Search For A Field

Mar 19, 2010

I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables

View 1 Replies View Related

SQL & PL/SQL :: Add A Mask To Field

Jun 23, 2010

need to add a mask to my field. I'm trying this:TO_CHAR (IMPORTADOR.RUC, .000.000./0000-00 '00 ') IMP_RUC,

View 2 Replies View Related

Forms :: One Lov For Different Field

Feb 22, 2011

i want to call same lov for 3 diff. field.....if possible how can i call it...because while desiging lov , returns item only 1 out of 3 field..how can it possible.

View 1 Replies View Related

SQL & PL/SQL :: Update A Field

Jun 16, 2011

Trying my best to follow the posting guidelines.

There are two tables.

orderheader and orderdetails. orderheader is a master table and orderdetails is a detail table. So for every one record in orderheader table, there are many records in orderdetails table. I want to update a field in orderdetails table from a orderheader table using inner join.

update orderdetails
set orderdetails.conversion_dt =
(select orderheader.conversion_dt
from orderheader, orderdetails
where orderheader.number= orderdetails.number )

I get the following error:

Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

*Cause:
*Action:

What mistake am I doing here?

View 16 Replies View Related

CTX-DDL And Blob Field?

Sep 28, 2012

Using Oracle 10g2 I'm trying to create a ctx index, using CTX_DLL atributtes.

begin
CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
end;

drop index se.INDEX_PRONUMJNE_CTX;

CREATE INDEX INDEX_PRONUMJNE_CTX on TBL_PRONUM (MyBlobColumn) INDEXTYPE IS CTXSYS.CONTEXT parameters('sync (on commit) LEXER LEXER_SINTILDES');However, I got errors:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: LEXER_SINTILDES
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Is it possible to create this kind of indexes for blob fields? Or just for varchar field

View 1 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

Oracle SQL Plus Inserting Into A New Field?

May 29, 2007

Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.

So Id basically be setting Release_confirmation to confirm for all existing rows.

Should I use update or insert?

View 2 Replies View Related

11g - Dynamic Field Reference

Jul 21, 2011

I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.

This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.

how to reference a field by its location or position in the query.

We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...

foreach($myrow as $field=>$value)

and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related

Execute UPDATE From Field That May Have More Than Row

Jun 29, 2010

I am trying to execute an UPDATE from a field that may have more than row. I get ORA-01427: single-row subquery returns more than one row.

I need to set the careof column in owner table to hold the name (NAM) from the alt table if the alttype from alt is ATT. We could have more than one id and the careof names can be different.

This is my query:

UPDATE owner s
SET s.careof = (SELECT a.NAM
FROM alt a
WHERE a.taxyr = '2011'
AND a.alttype = 'ATT'
AND a.card in ('0')

[code]....

View 1 Replies View Related

Get Left Chars Of A Field?

Oct 15, 2008

I have many different file names within my table and I want to remove the .TXT extension from each one. I want to try this SQL but being a newbie in Oracle, I don't know how to say "Left" characters. "Left" is an invalid identifier.

Update TableName
Set File_Name = Left(File_Name, Len(File_Name)-4)
Where File_Name LIKE '%.TXT'

View 2 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

I want to display Week No as the heading.So, I have these:

define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;

I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;

The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?

View 19 Replies View Related

SQL & PL/SQL :: Calculation On A Time Field?

Jul 5, 2012

I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.

I need to be able to determine the difference between column A and B eg.

Column A: Column B:
11:00 14:00

I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.

View 8 Replies View Related

SQL & PL/SQL :: Get MAX Length Of Column Value Along With Field Value

Oct 4, 2012

create table test_schema(
col1 varchar2(50)
)
insert all
into test_schema values ('this_is_a_test')
into test_schema values ('this_is_a_test_test')
into test_schema values ('this_is_a_test_test_xxxx')
into test_schema values ('this_is_a_test_test_aaaaaaaa')
select * from dual;

I want to get the length of the col1 value with maximum length of characters also with that field value.

i.e o/p is

this_is_a_test_test_aaaaaaaa length 28

how can I do it??

View 3 Replies View Related







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