Home » SQL & PL/SQL » SQL & PL/SQL » Oracle stored procedure with complex conditions
Oracle stored procedure with complex conditions [message #685158] Sun, 31 October 2021 21:36 Go to next message
sud_oracle
Messages: 3
Registered: October 2021
Junior Member
Hi Team,

I need to create a stored procedure to find out the final amount for each fund_id.Expecting around 500 records with different fund_id .Please find the below scenarios and help to see how i can achieve this.

1.select fund_id,amount as cap_amount from table1 where date=’01-01-2000’

2.select fund_id, amount as monthly_amount from table1 where date=’10-10-2010’

3.find new_amount = monthly_amount - cap_amount for each fund_id .(subtract for each id )

4.read mtd_amount from table2 for all fund_id .

5.select fund_id,base_amount from base_table (different table from above)

6.find final_amount = base_amount + new_amount - mtd_amount for all fund_id and insert into final_table.
Re: Oracle stored procedure with complex conditions [message #685159 is a reply to message #685158] Mon, 01 November 2021 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

What you posted is clear as mud!
What is "cap_amount"? What is "base_amount" What is "mtd_amount"? What is "base_table"? What is...

However, one important thing is that '’10-10-2010’' is not a date it is a string.
What date is '01-02-2020'? Jan, 2nd or Feb, 1st?

Re: Oracle stored procedure with complex conditions [message #685170 is a reply to message #685159] Mon, 01 November 2021 07:14 Go to previous messageGo to next message
sud_oracle
Messages: 3
Registered: October 2021
Junior Member
Hi MIchel,
Sorry for the confusion .These are details.

Using oracle version 12.1.0

Create Table AVERAGE_BALANCE(
FUND_ID NUMBER
GL_CODE VARCHAR
TL_DT TIMESTAMP
PRIOR_DAY_AMT DOUBLE
TOTAL_ADJ_VAL DOUBLE
)

INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
(10,’GE’,’14-JUN-21’,450,43),
(10,’GE’,’15-JUN-21’,350,53),
(10,’GE’,’30-MAY-21’,,110,5);

1.Find out MT_GL such a way that
Latest day value of PRIOR_DAY_AMT - last month recent value of TOTAL_ADJ_VAL
From above data
350 - 5 = 345 where
Recent day data is 350 and previous month TOTAL_ADJ_VAL is 5

2.
Create table TXN_HIST(
FUND_ID NUMBER,
SELL_DT TIMESTAMP,
TAX_CD varchar,
GAINLOSS DOUBLE
)

INSERT INTO TXN_HIST (FUND_ID,SELL_DT,TAX_CD,GAINLOSS) values(
10,’01-JUN-21’,’L’,11),
10,’02-JUN-21’,’L’,20),
10,’03-JUN-21’,’L’,23),
)
Find out all the GAINLOSS values for the fund_id where SELL_DT greater
Than prior period date (30-MAY-21)
In above example
11+20+23 = 54

3.
Create TABLE BASELINE(
FUND_ID NUMBER,
VERSION NUMBER,
SHORT_TERM_AMT DOUBLE,
LNG_TERM_AMT DOUBLE,
END_DT TIMESTAMP)
)
INSERT INTO BASELINE(FUND_ID,VERSION,SHORT_TERM_AMT,LNG_TERM_AMT,END_DT)
values(10,3,24,45,18-JUN-21),
(10,3,66,75,19-JUN-21);
FIND recent day data from baseline baseline =66

4.Calculate final amount

345+54+66 (from step1+step2+step3) =465
INSERT INTO OUTBOUND(FUND_ID,AMT) values (10,465);

Re: Oracle stored procedure with complex conditions [message #685174 is a reply to message #685170] Mon, 01 November 2021 09:57 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Before posting code, you should at a bare minimum test it for yourself.

Here's what I get after copying and pasting your code into a sql script and executing it:

SQL> show user
USER is "SCOTT"
SQL> Create Table AVERAGE_BALANCE(
  2  FUND_ID NUMBER
  3  GL_CODE VARCHAR
  4  TL_DT TIMESTAMP
  5  PRIOR_DAY_AMT DOUBLE
  6  TOTAL_ADJ_VAL DOUBLE
  7  )
  8  ;
GL_CODE VARCHAR
*
ERROR at line 3:
ORA-00907: missing right parenthesis


SQL>
SQL> INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
  2  (10,’GE’,’14-JUN-21’,450,43),
  3  (10,’GE’,’15-JUN-21’,350,53),
  4  (10,’GE’,’30-MAY-21’,,110,5);
INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
                                                                              *
ERROR at line 1:
ORA-00926: missing VALUES keyword


SQL> --
SQL> Create table TXN_HIST(
  2  FUND_ID NUMBER,
  3  SELL_DT TIMESTAMP,
  4  TAX_CD varchar,
  5  GAINLOSS DOUBLE
  6  )
  7  ;
TAX_CD varchar,
              *
ERROR at line 4:
ORA-00906: missing left parenthesis

SQL> INSERT INTO TXN_HIST (FUND_ID,SELL_DT,TAX_CD,GAINLOSS) values(
  2  10,’01-JUN-21’,’L’,11),
  3  10,’02-JUN-21’,’L’,20),
  4  10,’03-JUN-21’,’L’,23),
  5  )
  6  ;
10,’01-JUN-21’,’L’,11),
                     *
ERROR at line 2:
ORA-00917: missing comma


SQL> Create TABLE BASELINE(
  2  FUND_ID NUMBER,
  3  VERSION NUMBER,
  4  SHORT_TERM_AMT DOUBLE,
  5  LNG_TERM_AMT DOUBLE,
  6  END_DT TIMESTAMP)
  7  )
  8  ;
SHORT_TERM_AMT DOUBLE,
                     *
ERROR at line 4:
ORA-00905: missing keyword


SQL> INSERT INTO BASELINE(FUND_ID,VERSION,SHORT_TERM_AMT,LNG_TERM_AMT,END_DT)
  2  values(10,3,24,45,18-JUN-21),
  3  (10,3,66,75,19-JUN-21);
values(10,3,24,45,18-JUN-21),
                            *
ERROR at line 2:
ORA-00933: SQL command not properly ended
Let's dismantle that:

SQL> Create Table AVERAGE_BALANCE(
  2  FUND_ID NUMBER
  3  GL_CODE VARCHAR
  4  TL_DT TIMESTAMP
  5  PRIOR_DAY_AMT DOUBLE
  6  TOTAL_ADJ_VAL DOUBLE
  7  )
  8  ;
Type VARCHAR is only kept for backward compatibility. I don't even see it documented in the 12.1 SQL Language Reference. You should be using VARCHAR2. And either way, it requires a length designator.

Here's what the doc says about VARCHAR:
Quote:
VARCHAR Data Type

Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.
There is no data type DOUBLE documented in the reference.

SQL> INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
  2  (10,’GE’,’14-JUN-21’,450,43),
  3  (10,’GE’,’15-JUN-21’,350,53),
  4  (10,’GE’,’30-MAY-21’,,110,5);
INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
                                                                              *
ERROR at line 1:
ORA-00926: missing VALUES keyword
Not only is the syntax fundamentally wrong, you are not inserting a timestamp into the TL_DT column. You are inserting a string representation. You need to wrap that in a TO_TIMESTAMP function, with the appropriate format mask. On top of that you are recreating the Y2K bug. ALWAYS USE 4-DIGIT YEARS!!!!!!!!!!!!.

SQL> Create table TXN_HIST(
  2  FUND_ID NUMBER,
  3  SELL_DT TIMESTAMP,
  4  TAX_CD varchar,
  5  GAINLOSS DOUBLE
  6  )
  7  ;
TAX_CD varchar,
              *
ERROR at line 4:
ORA-00906: missing left parenthesis
Again, VARCHAR should be VARCHAR2(n), where (n) is the length designator. And again, there is no DOUBLE data type.

SQL> INSERT INTO TXN_HIST (FUND_ID,SELL_DT,TAX_CD,GAINLOSS) values(
  2  10,’01-JUN-21’,’L’,11),
  3  10,’02-JUN-21’,’L’,20),
  4  10,’03-JUN-21’,’L’,23),
  5  )
  6  ;
10,’01-JUN-21’,’L’,11),
                     *
ERROR at line 2:
ORA-00917: missing comma
You cannot insert multiple rows in a single INSERT like this. And if you could, look at your parings of left and right parentheses. And the extra comma at the end of line 4. And again, you are not inserting a date or timestamp, but a string representation of such. Again, you need to wrap it in TO_TIMESTAMP.


INSERT INTO BASELINE(FUND_ID,VERSION,SHORT_TERM_AMT,LNG_TERM_AMT,END_DT)
values(10,3,24,45,18-JUN-21),
(10,3,66,75,19-JUN-21);
FIND recent day data from baseline baseline =66
Here, your date/timestamp isn't even a string representation. Since it is not enclosed in single quotes, and includes hyphen/minus symbol, oracle will try to treat that as a math operation: 19 minus "JUN" (???) minus 21. Of course, due to the syntax errors it never got to actually try to evaluate that.

I'll quite there. It is obvious that you did not even bother to run your code, else you would have been asking about these errors instead of asking about how to write a query to get your desired result. It also appears that you never bothered to consult the SQL Language Reference, but are trying to write oracle code based on what you know from some other rdbms product.


[Updated on: Mon, 01 November 2021 10:00]

Report message to a moderator

Re: Oracle stored procedure with complex conditions [message #685175 is a reply to message #685174] Mon, 01 November 2021 10:25 Go to previous message
sud_oracle
Messages: 3
Registered: October 2021
Junior Member
@Edstevens,

Let me work on the sample and give you .Sorry for the inconvenience.
Previous Topic: Parsing query
Next Topic: How to get monthly average sum
Goto Forum:
  


Current Time: Fri Mar 29 02:27:26 CDT 2024