Home » SQL & PL/SQL » SQL & PL/SQL » Difficulty Framing a Query
Difficulty Framing a Query [message #249] Thu, 31 January 2002 20:39 Go to next message
Titoo
Messages: 4
Registered: January 2002
Junior Member
Dear Sir,
If you could help me out in the situation given below ,it would have been very kind of you.Actually, i am stuck at this point.I have encountered the following situation.

Everyday I upload the Trade Details for the previous day(say 30-jan-02) on today (say 31-jan-02) from a Database View named V_CONTRACTS((ONLY SELECT Privileges given to V_CONTRACTS) based on database Table TRADES in another schema) into another Database table named ORDER_INFO in the same Schema.
Actually Half of the Data is entered manually into TRADES on 30-jan-02 and the rest half of the data entered on 31-jan-02.
So, on 30-jan-02,i will upload the half of the data found in V_CONTRACTS into ORDER_INFO.
and on 31-jan-02, i must upload the rest half of the data found on 31-jan-02 from V_CONTRACTS into ORDER_INFO.

The Problem is that after I upload Data from V_CONTRACTS into ORDER_INFO on 30-jan-02,when i am trying to upload the remaining data on 31-jan-02, i must get only those data that was entered on 31-jan-02.
ie.I need to form a query in which i must retrieve only those records that is present in table TRADES (I will be using the view V_CONTRACTS) and not present in ORDER_INFO for the date 30-jan-02.
The problem is that there might be a situation where all the columns can have ALMOST the same value(s).
Data Manually entered on 30-jan-02 into TRADES
Eg:- Client_code =01A101
Trade_Date =30-jan-02
Scrip_code =483
Transaction_Type =1 --BUY
Executed_Qty =100
Executed_Gross_Price =1000.00
Executed_Net_Price =1050.00
Exchange =NSE
Seg_Type =1 --ROLLING

Data Manually entered on 31-jan-02 into TRADES
Eg:- Client_code =01A101
Trade_Date =30-jan-02
Scrip_code =483
Transaction_Type =2 --SELL (Only Column Value differing from the First Record )
Executed_Qty =100
Executed_Gross_Price =1000.00
Executed_Net_Price =1050.00
Exchange =NSE
Seg_Type =1 --ROLLING

Data Manually entered on 31-jan-02 into TRADES
Eg:- Client_code =01A101
Trade_Date =30-jan-02
Scrip_code =483
Transaction_Type =1 --BUY
Executed_Qty =100
Executed_Gross_Price =1000.00
Executed_Net_Price =1050.00
Exchange =BSE (Only Column Value differing from the First Record )
Seg_Type =1 --ROLLING

The Structure is given Below.
DESC V_CONTRACTS
Name Null? Type
------------------------------- -------- ----
CLIENT_CODE CHAR(6)
TRADE_DATE DATE
SCRIP_CODE CHAR(6)
TRANSACTION_TYPE VARCHAR2(1)
EXECUTED_QTY NUMBER(7)
EXECUTED_GROSS_PRICE NUMBER
EXECUTED_NET_PRICE NUMBER(10,4)
EXCHANGE CHAR(3)
SEG_TYPE CHAR(1)

DESC ORDER_INFO
Name Null? Type
------------------------------- -------- ----
ORD_CLIENT_ID VARCHAR2(10)
ORD_ORDER_DATE DATE
ORD_ASSET_CODE NOT NULL VARCHAR2(10)
ORD_TRANS_TYPE NOT NULL VARCHAR2(2)
ORD_QTY NUMBER
ORD_ALLOT_QTY NUMBER
ORD_ALLOT_PRICE NUMBER
ORD_EXECUTION_AMOUNT NUMBER(16,2)
ORD_EXCHANGE_CODE VARCHAR2(10)
ORD_SEGMENT_TYPE VARCHAR2(2)

If you could help me to frame this query ,it would have been much helpful.

Thanking you,
Regards,
Titoo Alfred
Re: Difficulty Framing a Query [message #262 is a reply to message #249] Fri, 01 February 2002 10:54 Go to previous message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi

In your example the V_contracts table has only 9 columns but order_info has 10 ..one extra column..

i am not sure its typo or thats your structure..
this is the code...you would use if they are same structure

insert into order_info
(select * from v_contracts
minus
select * from order_info)

this will insert those records which are in v_contrats but not in order_info..

if the structure between those two tables are different
then you have to use the detail column name instead of * like

insert into order_info
(select client_code,....... from v_contracts
minus
select ord_client_id from order_info);
Previous Topic: Restricting to update some rows in table
Next Topic: procedure question
Goto Forum:
  


Current Time: Fri Mar 29 05:51:12 CDT 2024