PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record
May 16, 2013
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
COMMIT;i need to break down the each member range by record per month and make a record as first day of that month . and dept and branch should have the same value as what the value it was in the range of source.
excepted ouput like below
EMP_ID MONTH_DAY_1 DEP_CODE BRANCH AAAA 01-JAN-2010 02 A AAAA 01-FEB-2010 02 A AAAA 01-MAR-2010 02 A AAAA 01-APR-2010 02 A AAAA 01-MAY-2010 02 A AAAA 01-JUN-2010 05 B AAAA 01-JUL-2010 05 B
.how can i code the logic to get my expected output above
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).
I've already achieve getting data from the previous month by using this formula:
My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?
I have an excel spreadsheet that queries an oracle data for some information. I am trying to get the information shown to only be from the current month. I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out. Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG; CREATE TABLE TEST_LOG (
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS = ( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0) FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL FROM ( SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
I am creating a record with the items - S_No, Product_Name, Selling_Qty, Price.
Checking the total qty (Tot_Qty) from the purchase stock - table1 and already sold out qty (Sold_Qty) of the product from table2.
Consider the following values: Tot_Qty = 200; Sold_Qty = 50;
Now i create a first record of the form as follows: here on selecting the Product_name from LOV, will display the Selling_Qty item - which can be edited (Selling_Qty = Tot_Qty - Sold_Qty) and its price.
Note: [value given within () are displayed automatically after selecting the prod]
My prob is, when i am entering next record, it displays the same 150 as qty, which have to be 100. After selecting the product from LOV, it should calculate & validate as shown below: which means, previous record value of Selling_Qty also have to be subtracted and displayed.
I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.
I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.
I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.
A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.
I have problem about displaying current records with same table(tbl_pawnitem). I have 2 transactions which are the pawned transaction and for sale transaction. and both transactions save into tbl_pawnitem table..i want to display only in my list the for sale transaction without deleting the records of pawned transaction. what condition i will use?
when I updated a record in my form check my screenshot: and clicked save button the result was:
as you can see in the message at the bottom, it has 18 records. And since my original data(subjects) are just 9. And also the grades that I input didn't display all.
This is my code in when-new-block-instance trigger: DECLARE CURSOR studgrade_cur IS SELECT e.student_id, s.subject_code --, g.grade
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
In my form line level block contains 100 records.i will check the check box for line number 96 and 97. Then i will press save(I have written some logic here) button it will generate one number for selected check boxes. After generating this number cursor(control) should be on same line number 96 or 97.
SELECT COUNT(*) INTO v_ctr FROM leave_type_govt_service WHERE ( :block_name.min BETWEEN minimum AND maximum [code]......
This code in my forms really works for inserting a new record. But if I will update the record itself for example, in the table 'Type 1'
Minimum Maximum 1 5 6 10 11 15
and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
my problem is this that i have developed a form.fmb in 6i for images scanning direct from scanner but its generating error something like that "FRM-41344 OLE not defined for SCAN in the current record"
li have a winxp enviroment i have already downloaded and installed "IMAGING FOR WINDOW"(GLOBAL 360) but despite this its not working same error is still there .
one more thing why imgscan.ocx is not registering in winxp.
