Home » SQL & PL/SQL » SQL & PL/SQL » Temporary table space error (Oracle 8i)
| Temporary table space error [message #637170] |
Tue, 12 May 2015 04:21  |
 |
harshal_lagwankar
Messages: 3
Registered: May 2013
Location: India
|
Junior Member |
|
|
Hi
I am running below query on year end DB(oracle 8i) and it throws and oracle error. I was wondering whether I can tune below query for Prod DBs. I have one inline view ST2 which is causing this problem. Since DB table stk_txns is huge it fails to retrieve data. But, in other DBs it runs smoothly due to relatively low records in stk_txns.
Here the requirement was if stock table has more than one record with same txn_date then we should select record with nominal holding date as 31/12/4712. So I first take count of records using ST2 view and if count is greater than 1 then apply that logic.
Any help would be appreciated.
SELECT st.br_id_code_clnt "Account ID",
st.stk_sedol_code_fls "SEDOL",
NULL "ISIN",
NULL "Country of Listing",
NULL "IMSSecRef",
st.txn_date "Transaction Date",
'G5' "IMS Transaction type",
CASE WHEN cnt > 1 THEN t.tot_qty ELSE st.tot_qty END AS "Quantity",
CASE
WHEN cnt > 1
THEN
cgt_book_cost
ELSE
CASE
WHEN SUBSTR (br.ag_id_code, 2, 1) IN
('A', 'B', 'C', 'D', 'J', 'P')
AND br.cou_code_tax_located IN
('110', '111', '112', '113', '121')
THEN
NVL (st.cgt_tot_book_cost, 0)
WHEN SUBSTR (br.ag_id_code, 2, 1) IN ('E', 'N')
THEN
NVL (st.cgt_tot_book_cost, 0)
WHEN SUBSTR (br.ag_id_code, 2, 1) IN
('A', 'B', 'C', 'D', 'J', 'P')
AND br.cou_code_tax_located NOT IN
('110', '111', '112', '113', '121')
THEN
NVL (st.tot_book_cost, 0)
+ NVL (st.ai_inclusive_amt_vln_mjc, 0)
WHEN SUBSTR (br.ag_id_code, 2, 1) IN
('F', 'G', 'H', 'M', 'P', 'R', 'W')
THEN
NVL (st.tot_book_cost, 0)
+ NVL (st.ai_inclusive_amt_vln_mjc, 0)
ELSE
st.cgt_tot_book_cost
END
END
"Expense",
NULL "Proceeds or Gross Income",
NULL "Indexation",
NULL "Index date",
st.id_code "IMS Transaction Ref.",
NULL "Revision Marker",
NULL "Narrative",
NULL "Pooling Date",
NULL "Reg'd Date",
NULL "Gain",
NULL "Gain Indexed Rise",
NULL "Gain Date",
NULL "Average Expense",
NULL "Transferee Code",
NULL "Suspect Marker",
NULL "March 82 Value",
NULL "March 82 Index",
NULL "Market Value",
NULL "Accrued Interest",
NULL "Currency ISO",
NULL "Local Amount",
NULL "Rate",
NULL "Holding",
NULL "Net Amount",
NULL "Tax Credit / Tax / Income Tax",
NULL "Value Date",
'1' "Company ID",
NULL "Corporate Action Ref"
FROM stk_txns st,
stocks s,
bus_roles br,
( SELECT COUNT (1) AS cnt,
txn_date,
br_id_code_clnt,
stk_sedol_code_fls
FROM stk_txns st, bus_roles br
WHERE st.txn_date <= TO_DATE ('05/04/2014', 'DD/MM/YYYY')
AND st.rc_code_st_txn_stat IN ('A', 'O')
AND st.ppln_id_code IS NULL
AND st.tot_qty != 0
AND st.br_id_code_clnt = br.id_code
AND br.brt_code_mibrt = 'CLIENT'
AND NVL (br.exp_date, '31-DEC-4712') > SYSDATE
GROUP BY txn_date, br_id_code_clnt, stk_sedol_code_fls
HAVING COUNT (1) > 1) st2,
(SELECT CASE
WHEN SUBSTR (b.ag_id_code, 2, 1) IN
('A', 'B', 'C', 'D', 'J', 'P')
AND b.cou_code_tax_located IN
('110', '111', '112', '113', '121')
THEN
NVL (t.cgt_tot_book_cost, 0)
WHEN SUBSTR (b.ag_id_code, 2, 1) IN ('E', 'N')
THEN
NVL (t.cgt_tot_book_cost, 0)
WHEN SUBSTR (b.ag_id_code, 2, 1) IN
('A', 'B', 'C', 'D', 'J', 'P')
AND b.cou_code_tax_located NOT IN
('110', '111', '112', '113', '121')
THEN
NVL (t.tot_book_cost, 0)
+ NVL (t.ai_inclusive_amt_vln_mjc, 0)
WHEN SUBSTR (b.ag_id_code, 2, 1) IN
('F', 'G', 'H', 'M', 'P', 'R', 'W')
THEN
NVL (t.tot_book_cost, 0)
+ NVL (t.ai_inclusive_amt_vln_mjc, 0)
ELSE
t.cgt_tot_book_cost
END
AS cgt_book_cost,
t.br_id_code_clnt,
t.stk_sedol_code_fls,
t.txn_date,
t.tot_qty,
b.managed_yn
FROM stk_txns t, bus_roles b
WHERE t.nom_hldgs_end_date =
TO_DATE ('31/12/4712', 'dd/mm/yyyy')
AND t.br_id_code_clnt = b.id_code
AND b.brt_code_mibrt = 'CLIENT'
AND NVL (b.exp_date, '31-DEC-4712') > SYSDATE) t
WHERE st.br_id_code_clnt = br.id_code
AND br.brt_code_mibrt = 'CLIENT'
AND NVL (br.exp_date, '31-DEC-4712') > SYSDATE
AND 1 = 1
--AND st.br_id_code_clnt = '101349'
AND st.txn_date <= TO_DATE ('05/04/2014', 'DD/MM/YYYY')
AND st.rc_code_st_txn_stat IN ('A', 'O')
AND st.ppln_id_code IS NULL
--AND st.tt_code IN ('B','S')
AND st.tot_qty != 0
AND st.stk_sedol_code_fls = s.sedol_code_fls
AND NVL (s.extinct_exp_date, '31-DEC-4712') = '31-DEC-4712'
--and st.STK_SEDOL_CODE_FLS = '0174154'
AND NOT EXISTS
(SELECT 1
FROM stk_txns st1, stocks s1
WHERE st1.br_id_code_clnt = st.br_id_code_clnt
AND st1.txn_date <=
TO_DATE ('05/04/2014', 'DD/MM/YYYY')
AND st1.rc_code_st_txn_stat IN ('A', 'O')
AND st1.ppln_id_code IS NULL
--AND st1.tt_code IN ('B','S')
AND st1.txn_date > st.txn_date
--AND ST1.TOT_QTY != 0
AND NVL (s1.extinct_exp_date, '31-DEC-4712') =
'31-DEC-4712'
AND st1.stk_sedol_code_fls = s1.sedol_code_fls
AND st1.stk_sedol_code_fls = st.stk_sedol_code_fls)
AND st2.br_id_code_clnt(+) = st.br_id_code_clnt
AND st2.stk_sedol_code_fls(+) = st.stk_sedol_code_fls
AND st2.txn_date(+) = st.txn_date
AND t.br_id_code_clnt = st.br_id_code_clnt
AND t.stk_sedol_code_fls = st.stk_sedol_code_fls
AND t.txn_date = st.txn_date
AND t.tot_qty != 0
AND st2.stk_sedol_code_fls = s.sedol_code_fls
Lalit : Added code tags
[Updated on: Tue, 12 May 2015 04:27] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Temporary table space error [message #637172 is a reply to message #637170] |
Tue, 12 May 2015 04:45  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I haven't studied your code in detail, but I think there is a basic programmer error. Look at the last few lines of your predicate: AND st2.br_id_code_clnt(+) = st.br_id_code_clnt
AND st2.stk_sedol_code_fls(+) = st.stk_sedol_code_fls
AND st2.txn_date(+) = st.txn_date
AND t.br_id_code_clnt = st.br_id_code_clnt
AND t.stk_sedol_code_fls = st.stk_sedol_code_fls
AND t.txn_date = st.txn_date
AND t.tot_qty != 0
AND st2.stk_sedol_code_fls = s.sedol_code_fls
You have an outer join that will generate null columns for the st2 table, and then an inner join to s. The inner join will filter out all the null rows generated by the outer join. So the outer join is useless. It will however be forcing a join order at may be sub-optimal. Get rid of it. This is another perfect example of why you should always use ANSI join syntax: such errors are much easier to see.
|
|
|
|
|
|
|
|
| Re: Temporary table space error [message #637182 is a reply to message #637180] |
Tue, 12 May 2015 06:27 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
An observation, this :
AND NVL (br.exp_date, '31-DEC-4712') > SYSDATE
is NLS dependent and not a good way of comparing dates. Always use TO_DATE to explicitly convert the literal into DATE.
For example,
SQL> alter session set nls_date_format='MM/DD/YYYY';
Session altered.
SQL> SELECT * FROM dual WHERE nvl(NULL, '31-DEC-4712') > SYSDATE;
SELECT * FROM dual WHERE nvl(NULL, '31-DEC-4712') > SYSDATE
*
ERROR at line 1:
ORA-01843: not a valid month
SQL>
|
|
|
|
|
|
|
|
| Re: Temporary table space error [message #637189 is a reply to message #637188] |
Tue, 12 May 2015 09:08 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
try
select txn-date,
br_id_code_clnt,
stk_sedol_code_fls,
st.nom_hldgs_end_date
from stk_txns st
where st.br_id_code_clnt = '101349'
and stk_sedol_code_fls = '0068707'
and nom_hldgs_end_date = to_date('12314712','mmddyyyy')
and exists
(select x.nom_hldgs_end_date
from stk_txns x
where st.br_id_code_clnt = x.st.br_id_code_clnt
and st.stk_sedol_code_fls = x.stk_sedol_code_fls
and xnom_hldgs_end_date <> to_date('12314712','mmddyyyy')
group by x.nom_hldgs_end_date
having count(*) > 1);
[Updated on: Tue, 12 May 2015 09:08] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Temporary table space error [message #637210 is a reply to message #637190] |
Wed, 13 May 2015 00:43 |
|
harshal_lagwankar
Messages: 3
Registered: May 2013
Location: India
|
Junior Member |
|
|
Hi,
If I use this in place of ST2 view then how can I use values in main Select. I need to pick up record from this view when txn_date is same for perticular client and sedol combination.
So before picking up records how can I check whether it has multiple records with same txn_date or not?
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:23:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
