| Difficulty Framing a Query [message #18465] |
Thu, 31 January 2002 20:45  |
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 #18472 is a reply to message #18465] |
Fri, 01 February 2002 00:17  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
A solution which should work is to select all from both tables data related to a specific day and then to find the difference (missing data) with the MINUS function.
select * from V_CONTRACTS
where ord_order_date=to_date('30-jan-02','DD-MON-YY')
MINUS
select * from ORDER_INFO
where trade_date=to_date('30-jan-02','DD-MON-YY')
HTH
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
