PL/SQL :: Model Clause Returning Phantom Value
Sep 25, 2012
I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.
DROP TABLE MORTGAGE_FACTS
CREATE TABLE mortgage_facts (
C_USER1 CHAR(15),
C_USER9 NUMBER,
C_USER11 NUMBER,
C_USER10 NUMBER,
C_B1 NUMBER(19,2),
C_B2 NUMBER(19,2),
C_BUDGET NUMBER(19,2),
C_USER12 NUMBER(19,2));
INSERT INTO mortgage_facts VALUES ('Jones', 131828.81, 3.348, 72, 0, 0, 0, 0)
--BEGIN AMORTIZATION
SELECT
P,
C_USER1,
C_USER9,
C_USER11,
C_USER10,
C_B1,
C_B2,
[code]........
The first two results look like this:
P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55
As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.
View 6 Replies
ADVERTISEMENT
Feb 19, 2010
I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:
SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)
SQL> select * from test1 order by id;
ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8
The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;
ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,
what I want is : null value should not come and duplicate value should not come (Value4 in output above)
View 11 Replies
View Related
Feb 21, 2013
Does the Oracle 11g supports 'RETURNING INTO' clause in MERGE statement? if it was not available is there any alternate to achieve the same ?
View 3 Replies
View Related
Nov 8, 2010
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 comp NEW YORK
20 Compt DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 Comp Ahaaa
80 data ab
80 data ab
80 data ab
80 data ab
9 rows selected.
s@ORCL> ed
Wrote file afiedt.buf
1 declare
2 type tp_dept is table of number;
3 arr_dept tp_dept;
4 begin
5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]'
6 returning into arr_dept;
7 dbms_output.put_line('The count is '||arr_dept.count);
8* end;
s@ORCL> /
returning into arr_dept;
*
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
View 4 Replies
View Related
Apr 5, 2010
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
View 22 Replies
View Related
May 1, 2013
We use apex and my professor wants us to import a table model into apex. everytime i try and import my table it says "your export file is not supported". i asked my professor what to do and he said "You should gen a ddl file from designer. Then use that to upload into apex and then run.
View 1 Replies
View Related
Jul 6, 2013
I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..
The 1st query is..
SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;
It created two user parameters.ie FROMDATE and TODATE.
And 2nd query like this
SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;
I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...
View 4 Replies
View Related
Sep 7, 2010
URL....Topic: The Execution Model for Triggers and Integrity Constraint Checking
Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:
1.Run all BEFORE statement triggers that apply to the statement.
2.Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.
3.Complete deferred integrity constraint checking.
4.Run all AFTER statement triggers that apply to the statement.
View 5 Replies
View Related
Mar 23, 2012
We would like to used Oracle secure backup using SAS interface on an IBM Autoloader Model TS-2900. Would it be possible to configure oracle secure backup using SAS?
View 7 Replies
View Related
Feb 3, 2013
I have developed a bitmap report on report 6i, But I am not able to print report on printer (Thermal Printer Model PRP-085III, A baylan POS printer).
I tried to convert the same report to Character mode and it print fine.
why it is not printing or what I need to set ?
View 4 Replies
View Related
Nov 24, 2012
For applications and Timesten databases on the same server we can use direct model to gain the base performance. But I want to know that how big heap size of JVM to be set for my java application when enabling direct model?
Does my application need more head memory when direct model than other local communication protocols, such as Unix domain socket or IPC? Supposing my Timesten database takes 12GB memory from OS, does it mean I need specify the same size for JVM heap(-Xmx12G)?
View 2 Replies
View Related
Dec 19, 2012
I'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.
View 8 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Jan 2, 2012
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
View 9 Replies
View Related
Mar 22, 2012
create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)
My requirement is to return
id key
1 10
1 10
because both their id and key are same
View 2 Replies
View Related
Jan 24, 2013
I have the below requirement,
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
View 2 Replies
View Related
Jun 5, 2013
I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order
select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;
but this seems to return more than one entrty:
table date below:
drop table employee_list;
drop table employee_historic;
[code]...
What is the best way to return a single row, if the primary key is not available / applicable.
View 9 Replies
View Related
Nov 5, 2012
I have this query to return to me the latest case note, now I would like to add the date prior to the latest case note.
select case_notes.applicant_id,
reg.program_code,
reg.last_name,
[Code].....
View 1 Replies
View Related
Mar 1, 2011
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;
View 3 Replies
View Related
Jan 16, 2011
I run following query.
select to_date(sysdate,'DD/MM/YYYY') Dte FROM DUAL
Result is
Dte
--------
01/17/0011
why it is not returning '01-17-2011'?
View 3 Replies
View Related
Nov 20, 2010
how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :
If I've got table
customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
View 9 Replies
View Related
Mar 27, 2013
I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...
select hour,
calls
from
(
select hour,
calls,
rank() over (ORDER BY calls desc) as ranking
from
(
[Code]....
View 1 Replies
View Related
Oct 1, 2013
I'm trying to query an array where we will have mathnames that have the follow format: variablemathname[00000]
have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.
In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]
I tried using the escape before my pattern as I know that [ is a metacharacter but I receive the same results.i'm trying to use to get the expression correct.
SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT
FROM DUAL;
My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.
View 10 Replies
View Related
Jan 19, 2012
Imagine I have the following scenario:
CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');
Imagine I want to retrieve those records in only 3 distinct rows, like this:
Munich
Madrid
Others
Is there any way I can do this with an simple Statement?
View 6 Replies
View Related
Apr 4, 2010
I have following code. It is always returning sysdate even run on Sunday.
declare
vDate date;
begin
if to_char(sysdate,'Day')='Sunday' THEN
vDate := sysdate-3;
[Code]...
if there is something missing or wrong.
View 2 Replies
View Related
Sep 9, 2009
I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:
update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)
It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.
View 2 Replies
View Related
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
Jan 30, 2007
I work on a client-server application, where users need to be able to run rather complex queries.
We currently have most of the queries defined in views on the Oracle database server and the client application simply downloads the data (i.e. SELECT * from example_view). This is good for us as we can maintain these queries without releasing new versions of the client tool.
However we have some queries implemented by a colleague that have caused a lot of trouble (efficiency and quality) and these are stored client-side.
The issue I have is that these client side queries can return records in different units (i.e. in standard cubic metres, or barrels of oil etc), as the SQL is defined at runtime on the client, and I want to know the best way to replicate this with SQL stored server-side.
The client-side SQL has column definitions such as: SELECT oil_production * decode(&unit,'Nm3',.948,'Sm3',1,'MMBOE',6.0924,1) ... The &unit parameter is then replaced by the appropriate text (i.e. 'Sm3') before the query is sent to Oracle.
Is there anyway to pass variables to server-side SQL and get a recordset back? I don't think PL/SQL procedures can do this? and views can't contain bind variables.
View 1 Replies
View Related
Oct 23, 2009
I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):
create or replace procedure p_xml_test_1(
p_xml out nocopy clob
) is
begin
p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
'<test><something>some value</something></test>';
end p_xml_test_1;
But I have access to some other source code that basically does this:
create or replace procedure p_xml_test_2(
p_xml out nocopy clob
) is
lv_xml clob;
begin
dbms_lob.createtemporary(
[code]......
I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any, to the second method?
View 1 Replies
View Related
May 4, 2010
I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....
How do I return the table (referenced by a cursor) without creating objects?
View 1 Replies
View Related