|
|
|
|
| Re: New developer and facing trouble with a store procedure [message #625209 is a reply to message #625207] |
Thu, 02 October 2014 12:29   |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
I'm sorry, but my workplace (as do many others) blocks the download of attached files. Why can't you just put a little sample directly int your post, enclosed by "code" tags?
Without looking any further, your approach of constantly updating 'end-date' to the current date is a serious design flaw. What is the purpose of doing so? Because an 'end date' hasn't yet been established? If so I would leave the column null until such time as it is established.
If you are having trouble with the PL/SQL, show us your work so far. Many people will be more than happy to help out, but no one worth their salt is going to write it for you. It's much easier for us to correct code that misses the mark than to make a wild guess at something.
[Updated on: Thu, 02 October 2014 12:31] Report message to a moderator |
|
|
|
| Re: New developer and facing trouble with a store procedure [message #625211 is a reply to message #625209] |
Thu, 02 October 2014 13:36 |
 |
new_techie_14
Messages: 3
Registered: October 2014
Location: US
|
Junior Member |
|
|
My sample data:
DAY 1:
TYPE_KEY PRODUCT_KEY DATE START_DATE END_DATE
2 12 10/1/2014 10/1/2014 10/1/2014
2 45 10/1/2014 10/1/2014 10/1/2014
2 23 10/1/2014 10/1/2014 10/1/2014
2 90 10/1/2014 10/1/2014 10/1/2014
DAY 2:
TYPE_KEY PRODUCT_KEY DATE START_DATE END_DATE
2 12 10/2/2014 10/1/2014 10/2/2014
2 45 10/2/2014 10/1/2014 10/2/2014
2 23 10/2/2014 10/1/2014 10/2/2014
2 90 10/2/2014 10/1/2014 10/2/2014
DAY 3:
TYPE_KEY PRODUCT_KEY DATE START_DATE END_DATE
2 12 10/3/2014 10/1/2014 10/3/2014
2 45 10/3/2014 10/1/2014 10/3/2014
2 23 10/3/2014 10/1/2014 10/3/2014
2 90 10/3/2014 10/1/2014 10/2/2014 - this product isn't linked to that type anymore
So far this is the way I think it should be done.
PROCEDURE TYPE_PRODUCT_REL
MERGE INTO TYPE_PRODUCT_RELATION TPR
USING
(
SELECT TYPE_KEY,
PRODUCT_KEY
FROM TYPE T JOIN PRODUCT P
ON T.PRODUCT_ID = P.PRODUCT_ID
)SRC
ON (TPR.TYPE_KEY = SRC.TYPE_KEY
AND TPR.PRODUCT_KEY = SRC.PRODUCT_KEY
)
WHEN NOT MATCHED THEN
INSERT
(
TYPE_KEY,
PRODUCT_KEY,
DATE,
START_DATE,
END_DATE,
INSERT_TIME,
LAST_UPDATED_BY
)
VALUES
(
SRC.TYPE_KEY
SRC.PRODUCT_KEY,
TRUNC(SYSDATE),
TRUNC(SYSDATE),
TRUNC(SYSDATE),
SYSTIMESTAMP,
USER
)
WHEN MATCHED THEN
UPDATE
SET
DATE= TRUNC(SYSDATE),
END_DATE = TRUNC(SYSDATE),
LAST_UPDATED_TIME = SYSTIMESTAMP;
COMMIT;
Even I don't agree with the end date constantly updating, but that's the requirement.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
