Home » Other » Test » DML for test data (PL/SQL 10.2.0.4.0)
DML for test data [message #396436] Mon, 06 April 2009 12:16 Go to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
I'm not sure if i'm posting or wording this question right, so here's a test...

I have a file which I made to a table with fields/columns:

Table: Test1
Columns:vendor_no, vendor_name, item_no, customer_no

I need to make a join to another table (test2) to find the most recent records before a certain date for price, rules and date.

The table I will be joining is below and has columns:

Table: Test2
Columns: item_no, customer_no, sell_price, price_code, contract_cost, effective_date, termination_date,etc

What I want to do is join item_no from test1 to item_no on test2 and join customer_no from test1 to customer_no from test2 to find the the contract effective date as of 1/1/2009. Since contract dates can be updated with new dates, I want to restrict so that I only get the most recent records before 1/1/2009.

I've got this far (query below) but I'm stuck as far as adding the next step to restrict the date. I may be taking the wrong approach on this:

select d.vb_no, 
       d.vendor_name, 
       d.item_no, 
       d.customer_no, 
       p.sell_price, 
       p.price_code, 
       p.contract_cost, 
       p.eff_date, 
       p.term_date, 
       p.current_flag
from test1 d, test2 p
where d.item_no=p.item_no
and d.bill_to=p.customer_no


[Updated on: Mon, 06 April 2009 12:21]

Report message to a moderator

Re: DML for test data [message #396439 is a reply to message #396436] Mon, 06 April 2009 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Do you know what DDL & DML are?
WIKIPEDIA has answers!


Re: DML for test data [message #396459 is a reply to message #396439] Mon, 06 April 2009 18:31 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
After reading, I know what the acronym stands for but nothing beyond that. Sad

Desired result is

New Table:

vendor_no, vendor_name, item_no, customer_no (from test1)
and
sell_price, price_code, contract_cost, effective_date, termination_date (from test2)

Re: DML for test data [message #396460 is a reply to message #396436] Mon, 06 April 2009 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

As re-stated below


Practice

Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.
Re: DML for test data [message #396461 is a reply to message #396460] Mon, 06 April 2009 18:51 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
ok so maybe that's my weakness... among many others Sad

I haven't use the insert statement or the create table. I've been using oracle as a simple select statement for reporting purposes. The first post is really my script or DDL that I use.
Re: DML for test data [message #396468 is a reply to message #396436] Mon, 06 April 2009 20:16 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/Test_case

Once again, many or most answers can be found on this site if you would actually make some effort to SEARCH!
Previous Topic: g
Next Topic: merged topic1 and topic2
Goto Forum:
  


Current Time: Fri Mar 29 06:13:01 CDT 2024