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 Go to next message
buggleboy007
Messages: 268
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;

Select * from cmdty_cp

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 #654971 is a reply to message #654927] Wed, 17 August 2016 16:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Not everybody can download or read attachments. It is best to put everything in your post. If you have to use an attachment, then at least use a text file that more people can download and read.

Your insert statements should include everything that is needed for testing. You should also include an example of desired results that corresponds to your description of desired results and your sample inserted data.

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?

You need to provide a better example, because it is not possible to understand what you want from what you have posted.
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 Go to previous message
buggleboy007
Messages: 268
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!

Previous Topic: previous data
Next Topic: ORA-01917 ERROR
Goto Forum:
  


Current Time: Sun Jun 13 15:06:43 CDT 2021