Home » SQL & PL/SQL » SQL & PL/SQL » How to get a value that has never been updated (Oracle Database:11.2.0.1.0; TOAD: 12.1, O/S: Windows 7 Professional 64 bit)
| How to get a value that has never been updated [message #654927] |
Wed, 17 August 2016 02:12  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
I have a table called CMDTY_CP whose structure is as follows:
STCC_CD VARCHAR2(7 BYTE) NOT NULL,
VRSN_NBR NUMBER(5) NOT NULL,
GROUP_CD VARCHAR2(2 BYTE),
SBGRP_CD VARCHAR2(1 BYTE),
PRICE_USAGE_IND VARCHAR2(1 BYTE) NOT NULL,
CMDTY_SHORT_NM VARCHAR2(6 BYTE),
CPRS_CMDTY_DSC_TXT VARCHAR2(30 BYTE)
So in other words when I issue
(PROOF OF TABLE CREATED AND VALUES INSERTED ETC., HAS BEEN ATTACHED AS A WORD FILE(screenshots.doc). This word file has all the screenshots.
CREATE TABLE CMDTY_CP
(
STCC_CD VARCHAR2(7 BYTE) NOT NULL,
VRSN_NBR NUMBER(5) NOT NULL,
GROUP_CD VARCHAR2(2 BYTE),
SBGRP_CD VARCHAR2(1 BYTE),
PRICE_USAGE_IND VARCHAR2(1 BYTE) NOT NULL,
CMDTY_SHORT_NM VARCHAR2(6 BYTE),
CPRS_CMDTY_DSC_TXT VARCHAR2(30 BYTE)
)
the table gets created.
I now insert the following values and commit it:
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113530',0,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113530',1,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113530',2,'02',0,'U','RYE','OTHER GRAIN');
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',0,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',1,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',2,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',3,NULL,NULL,'N',NULL,NULL);
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',4,'01',0,'U','WHEAT','WHEAT');
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',0,NULL,NULL,'N',NULL,'ESCAROLE, FRESH');
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',1,NULL,NULL,'N',NULL,'ESCAROLE, FRESH');
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',2,NULL,NULL,'N',NULL,'ESCAROLE, FRESH');
INSERT INTO CMDTY_CP (STCC_CD,VRSN_NBR,GROUP_CD,SBGRP_CD,PRICE_USAGE_IND,CMDTY_SHORT_NM,CPRS_CMDTY_DSC_TXT) VALUES ('0113740',3,NULL,NULL,'N',NULL,'ESCAROLE, FRESH');
COMMIT;
Once committed, I was initially given a requirement as follows:
Find all the STCC_CD with maximum VRSN_NBR where the GROUP_CD is NULL and CMDTY_SHORT_NM is NULL and also VRSN_NBR IS GREATER THAN 0.
So I used the following query to find the answer:
SELECT *
FROM(SELECT A.*,MAX(vrsn_nbr) OVER (PARTITION BY stcc_cd) MAXIMUM_VERSION
FROM cmdty_cp A
WHERE
A.group_cd IS NULL
AND
A.cmdty_short_nm IS NULL
AND
A.vrsn_nbr > 0)
WHERE vrsn_nbr = MAXIMUM_VERSION
ORDER BY stcc_cd, vrsn_nbr asc;
The above query worked well. No issues. User was also happy.
However the requirement changed slightly during the course of the day and I was asked to fetch the maximum version where in there was never an update to the STCC code. This means I should in fact be seeing only 1 STCC_CD value i.e. 0133920 as opposed to the other two (because I am told that business needs the value which was never ever updated). This means that STCC_CD = 0113530 and 0113740 was updated in the past because they have values that were updated in the past (version number =2 with group code = 02 for STCC 0113530 and version number 4 with group code 01 for STCC 01133740, where as for STCC 0133920 there was never an update based on the fact that none of the version numbers had any update of group code).
So I basically need only one value(STCC CODE) with maximum version number which in this case will be 0133920 with version number =3.
Is there a way to achieve this tweaking or using correlated query? I tried a few but hit a roadblock. Can any one of you help me out?
|
|
|
|
|
|
| Re: How to get a value that has never been updated [message #654972 is a reply to message #654971] |
Wed, 17 August 2016 21:33  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Barbara Boehmer wrote on Wed, 17 August 2016 16:02
Your explanation does not seem to make sense. You refer to 0133920 but there is no such value in your insert statements. You cannot have that in your results if it is not inserted. You say that you want the row with the highest version that has never been updated. If it has anything greater than the lowest version number does that not constitute an update? What constitutes an update?
Indeed you are correct Barbara. I did not include 0133920 as STCC CODE in my script. The rows that have "ESCAROLE, FRESH" with STCC code as 0113740 should have been read as 0133920.Apologies from my side.
With respect to the document, I was not aware that a word document may have an issue as far as downloading was concerned. Point noted.
I managed to tweak the query and get the desired output. Thanks a lot Barbara for your time on this!
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 01:56:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
