SQL & PL/SQL :: Compare 2 Tables Based On 2 Columns

Oct 19, 2010

My tables looks like this:

Desc Table A (account)
Account1_id
Account2_id
name,
empid

Table B (Bill )
BillNo
Advertiserid
agencyid
total vvalue

I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.

In other words my output should be like

Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....

objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .

I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:

select.......from a,b
where b.advertiser_id = a.account1_id and b.agencyid = b.account2id

Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Compare 2 Tables On Columns Value?

Oct 25, 2010

I have to compare 2 tables on a columns's value.The 2 tables have same column as Regn_no.I just need to have the result in True Or False from a Query if the particular value of Regn_no is found in both the tables.

Temporarily I using a bit different and not good query which is as follows:

select count(*) from tab1 where regn_no in (select regn_no from tab2) and regn_no = 'UP78AN7890';

View 2 Replies View Related

Compare Two Columns Relationship

Apr 29, 2011

I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.

Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058

I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b

where a.Col1 = 11060
and a.Col1=b.Col1
and a.Col2 <> b.Col2
order by Col1, Col2
[code]....

HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.

View 1 Replies View Related

SQL & PL/SQL :: Compare Columns Of Two Table?

Apr 5, 2013

I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..

View 17 Replies View Related

SQL & PL/SQL :: To Compare Records In Two Columns

Oct 8, 2010

I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.

For instance:
'MAPE STUD' <-> MAPE STUDY

And I should see that these records are the same because different is only in the one word.

View 31 Replies View Related

SQL & PL/SQL :: How To Compare The Four Tables

Jan 5, 2013

I Have Four Tables

1) Sal_master
structure is voc_no varchar2(7),voc_date date

2) sal_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

3). delivery_master
structure is voc_no varchar2(7),voc_date date;

4) delivery_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example

Voc_no Sale Qty Deliver Qty Remaining Qty

S000075 10 5 5 if data not found from delivery master and detail then answer must be
S000075 10 0 10

View 2 Replies View Related

Compare A Date In One Of Tables To Sysdate?

Mar 5, 2012

i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results

View 2 Replies View Related

SQL & PL/SQL :: Compare Two Tables Having Same Structure In Two Different Servers

Jun 4, 2012

I am working in a reporting project.We have different environments.After migration of data base script from one server to another we always need to crosscheck whether newly added columns have been properly migrated or not.

any database script to address the same thing. Last but not the least we have servers with TNS entries. how we can connect to different server while present in one specific server.

View 5 Replies View Related

Forms :: Compare Two Tables With Rowid

Nov 1, 2013

is it possible to compare two tables with rowid? I want to put rowid in where condition...how can we do this?

View 4 Replies View Related

SQL & PL/SQL :: How To Add A CHECK CONSTRAINT To Compare Info In 2 Tables

Jan 27, 2011

I need to alter a table to check that the data in a column is contained in a similar column in another table.

I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.

I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.

Both have a NUMBER(5) data type.

Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?

View 2 Replies View Related

PL/SQL :: Compare Two Tables And Delete The Common Rows From Table 1

Jul 23, 2012

I want to compare two tables , and delete the common rows from the first table

Here is what i have done :

Create table test1(Test1C1 Number,
Test1C2 varchar2(50));

Create table test2(Test2C1 Number,
Test2C2 varchar2(50));

Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......

it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?

The desired contents in table test1 will be

2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10

View 5 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.

View 8 Replies View Related

SQL & PL/SQL :: Aggregation Two Different Columns Based On Same Condition?

Apr 3, 2011

I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.

Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...

Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...

Quote:
SELECT
REGION,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,

[code]...

My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...

View 2 Replies View Related

SQL & PL/SQL :: Perform SUM On Two Columns Based On Level?

Oct 21, 2011

Below is the code that i tried to perform the sum operation.

CREATE TABLE TEST11
(
FISCAL_TIME_ID NUMBER,
data_id number,
M_VALUE NUMBER,
Y_VALUE NUMBER
);

[code].....

The result I got is

SQL> SELECT FISCAL_TIME_ID, DATA_ID, M_VALUE,
2 SUM(m_value) OVER (PARTITION BY fiscal_time_id, data_id
3 ORDER BY FISCAL_TIME_ID) AS YTD_VALUE
4 from test11;

[code].....

But what I am actually want to get is.

FISCAL_TIME_ID DATA_ID M_VALUE YTD_VALUE
-------------- ---------- ---------- ----------
20110500 3 2 2
20110700 3 50 52
20110800 3 52
20111000 3 250 352
20111100 3 300 652

That is, the YTD_Value column is nothing but sum of M_VALUE column + previous fiscal_month_id's ytd_value column.

Test case:

The YTD_VALUE for fiscal_time_id 20110700 is obtained as current M_VALUE + previous fiscal_time_id's ytd_value => 50 + 2

I tried with the SQL but i could not get the result.

View 5 Replies View Related

SQL & PL/SQL :: Return Results Based On 2 Columns?

Jul 15, 2013

I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.

Emplid Effdt Effseq

10001 '01-JAN-99' 0

10001 '01-JUL-11' 0

10001 '01-JUL-11' 1

10001 '01-JUL-11' 2

10001 '01-JUL-12' 3

What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker.

View 7 Replies View Related

SQL & PL/SQL :: Compute Based On Columns As Variables

Jul 1, 2012

My issue is with a calculation using columns as variables. below is the code to create and attached is the code (INSERT_data.txt) to fill-in the required tables....

what's required here?

I need to update (through update or simply create new table statement)

BPF(i), BPC(i), BPY(i) of table root_tbl (A) based on InF_IDX, InC_IDX & InY_IDX and OutF_IDX, OutC_IDX & OutY_IDX using BPF(i), BPC(i), BPY(i) from table pvt_vectors_tbl(B).

Join Clause: A.P_NODE=B..P_NODE and
A.C_NODE=B.C_NODE and
A.P_NODE_Date=B.P_NODE_Date and
A.C_NODE_Date=B.C_NODE_Date

the formula are:

BPF(i):
if A.InF_IDX<=A.OutF_IDX then
FOR i=1 to A.OutF_IDX+1
if i<InF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(OutF_IDX + i - InF_IDX); else A.BPF(i)=0;
i=i+1;
else
FOR i=1 to A.InF_IDX+1
if i<OutF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(InF_IDX + i - OutF_IDX); else A.BPF(i)=0;
i=i+1;
idem for BPc and BPY.

in real word:

* root_tbl table has ~2 million records, 50 BPF(i) columns, 50 BPC(i) columns and ~475 BPY(i) columns
* pvt_vectors_tbl has ~50.000 records

/*create and fill-in pvt_vectors_tbl table*/

CREATE TABLE root_tbl
(
P_NODE VARCHAR2(3),
C_NODE VARCHAR2(3),
P_NODE_Date date,
C_NODE_Date date,
InF_IDX number,
InC_IDX number,
InY_IDX number,
[code].........

View 2 Replies View Related

PL/SQL :: Delete Dupes Based On Multiple Columns

Dec 28, 2012

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I want to delete dups from a table based on 3 columns

with sample_table as (
select '101' as ID1, '201' as ID2, '4' as weight  from dual union all
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
[code].........                     

Desired Output

with sample_table as (
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '110' as ID1, '215' as ID2, '9' as weight  from dual
)
select * from sample_table

View 5 Replies View Related

Reports & Discoverer :: How To Skip Columns With Space Based On Parameters

Jan 10, 2011

I have Two Parameters like 'YES' & 'NO'

when parameter is YES, column having values
when Parameter is NO, column having no values

In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.

View 9 Replies View Related

Application Express :: Disable Report Columns Based On Item Value

Jul 25, 2013

I've got a report with two lov's, where the user is able to change the lov value and submit it. After submit the status of the item P100_status will be changed in Disable. Based on this value the lov's must be disables with apex_disabled.  How can I disable these columns based on the value of P100_status?

View 11 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

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

SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition

Nov 26, 2010

have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )

Query 1-SO
-----------------------------------
select
so.SMO_NO,
so.SPLY_ORD_DT,
so.fk_CUST_ID as CUST_ID,
so.CUST_PO_NO,
so.ATTENTION_NAME,
[code].....

Query-2 Xcom
--------------------------------------
select
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,
xsol.CARTONS_ORDERED as SPLY_ORD_QTY,
[code].......

Now the requirement is
One of four conditions are possible for each Supply Reorder Number:

. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER.
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.

Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.

View 7 Replies View Related

SQL & PL/SQL :: To Delete Data From Tables Based On A Condition

Feb 3, 2013

i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.

View 17 Replies View Related

SQL & PL/SQL :: Merge Columns Of 2 Tables?

Apr 14, 2011

i wanted the query for the followin example

sample1
A B C D E F
--------------------------
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5

sample2
G H I J
----------------
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5

consider the above tables what i want is some thing like this

output
G H I J A
-----------------

this can also be done through a select statement through choosing the columns which you want i don want thatbut what i want is columns of entire table sample2 and only one column in sample1

View 5 Replies View Related

SQL & PL/SQL :: ORA-30506 - System Triggers Cannot Be Based On Tables Or Views

Jan 24, 2012

i'm trying to audit ddl actions(a strange "disappearing of a table every couple of days) on a specific table.

i want to trigger those actions, but not on a database level, but a table's level only(or at least a user schema's).

when i execute

CREATE OR REPLACE TRIGGER system.audit_trg BEFORE DROP ON tu2.d
BEGIN
INSERT INTO

[Code].....

i get an error
ORA-30506: system triggers cannot be based on tables or views

and if it is - how do i adjust my plsql?

View 29 Replies View Related

SQL & PL/SQL :: How To Find Tables Which Does Not Have Specific Columns

Jan 11, 2013

I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.

View 8 Replies View Related

PL/SQL :: How To Retrieve Information About Columns Of Tables

Nov 16, 2012

I am trying to get information about the columns of tables in Oracle databases. In MySQL, show columns provides good deal of information.But in Oracle, we do not have such equivalent call."describe" shows only the primary key.

But it does not show whether any column has unique constraint or an index.

I created a column in a table with unique constraint (but not a primary key), but it was not listed with CONSTRAINT_TYPE = 'U' in dba_constraints view, but was listed with CONSTRAINT_TYPE = 'C' - Check constraint.

View 6 Replies View Related

Server Utilities :: How To Export Data Based On Join Between 3 Tables

Jan 10, 2011

How to export a data, which is a join of three tables.Will Export or dbms_datapump supports for above scenario.

Database: DB1
Tables: T1, T2 & T3
Select: t1.*,t2.*,t3.*
Join: t1.c1=t2.c1 and t2.c1=t3.c1

View 2 Replies View Related

SQL & PL/SQL :: Swap Values Between Columns Across Tables In Database

Jan 28, 2011

I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.

Before Swaping:

Columns Values
TABLE1.IDENTIFIER MRN123
TABLE1.MEDICARENO CHI1234567
TABLE2.MRN MRN123
TABLE3.MRN MRN123
OTHERTABLE.MRN MRN123

After Swapping:

Columns Values
TABLE1.IDENTIFIER CHI1234567
TABLE1.MEDICARENO MRN123
TABLE2.MRN CHI1234567
TABLE3.MRN CHI1234567
OTHERTABLE.MRN CHI1234567

View 12 Replies View Related

SQL & PL/SQL :: Large Tables / Remove Unnecessary Columns

Mar 14, 2011

I have a large table with 450 column and we are only using nearly 170 columns and our BD block size is 8k.The DBA informed that there is an row chaining happening in the Database.My question is if we have data available in 170 column .why row chaining is happening.

The DBA informed us to remove the unnecessary columns .. Does those empty columns have any impact on the chaining.If we increase the size of DB block to 32k . does it will resolve the issue.

View 4 Replies View Related







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