• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Wrong record extracting - DB2 SQL

Hello,

I am trying to figure out how to pull the last of all records that an extract I have written. I am using MAX and it pulls the first record not the last. In the attached sample the first data row (line 2) is what I am getting in my final results. What i really want is the last line (row 4)... what am I doing wrong.


delete from  VD_GPL.AGNITEMALLOCATIONKEY;


create table qtemp.items(
itemallocationid char(10) default ' '
,itemId char(20) default ' '
,displayproductnumber char(30) default ' '
,INVENTDIM_CONFIGID char(15) default ' '
,dataareaid char(4) default ' ' 
,locationdescription char(30) default' '
,toteinsertid char(10) default '  '
,totesid char(10) default ' '
,unitpertote integer default 0
,configid char(15) default ' '
,inventsiteid char(10) default ' '
,somiitemid char(20) default ' '
,imprfg char(3) default ' '
,rtrcode char(9) default ' ' 
,imsts char(1) default ' ' 
,imtrnr char(10) default ' '
,lmpn char(15) default ' '
,lmcode char(9) default ' '
,lmco decimal(3) default 0
);


insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco)

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,lmco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco = (1) AND lmco = (1) and 
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco)

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,lmco
from AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco = (3) AND lmco = (3) and 
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID  AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID)
);

insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco)

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,lmco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco = (5)  AND  lmco = (5) and
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID )
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco = (8)  AND lmco = (8) and
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID )
);

select * from qtemp.items where itemid = '4270107286' OR '964202-00';

UPDATE qtemp.items a
SET locationdescription =  
 
 CASE
 WHEN
             (SELECT MAX(trim(LMCODE)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) > ' '  
            
THEN
             (SELECT MAX(trim(LMCODE)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) 
               ||' : '||
              (SELECT SUBSTR(lmsdes,1,10) 
              FROM   kbm400mfg.fklocmst 
              WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.lmco = lmco)
 
ELSE
               (SELECT trim(lmcode) FROM kbm400mfg.fklocmst b  
                WHERE b.lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and b.lmcode = a.lmcode)
                ||' : '||
                 (SELECT SUBSTR(lmsdes,1,10) FROM   kbm400mfg.fklocmst b
                    WHERE  lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and a.lmcode = b.lmcode)                                     
 
END;       
					  
select COUNT(*) from qtemp.items;
select * from qtemp.items where itemid = '4270107286';
select * from qtemp.items;

Insert Into VD_GPL.AGNITEMALLOCATIONKEY
(InventDimid
,ItemallocationId
,Itemid
,LocationDescription
,toteinsertid
,totesid
,unitpertote
,configid
,inventsiteid
,SomiItemId
)
(
Select 
INVENTDIM_CONFIGID
,itemallocationid
,Itemid
,IfNull(locationdescription, ' ')
,IfNull(toteinsertid, ' ')
,IfNull(totesid, ' ')
,IfNull(unitpertote, 0)
,configid
,inventsiteid
,IfNull(SomiItemId,' ')
from qtemp.items);

Delete from  VD_GPL.AGNITEMALLOCATIONKEY  a where RRN(a) > (select MIN(RRN(b)) from VD_GPL.AGNITEMALLOCATIONKEY  b where  a.Itemid = b.Itemid  );
select  PURCHID , count(*) from VD_GPL.DMFPURCHTABLEENTITY  group by  PURCHID  having count(*) > 1;
 

SELECT COUNT (*) FROM VD_GPL.AGNITEMALLOCATIONKEY;

Open in new window

sample.xlsx
0
Leogal
Asked:
Leogal
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi leogal,

Unless something in the data can be used to sequence the rows, you can't determine which row was inserted first, last, or anywhere in between.

The easiest way around this is to add an IDENTITY column to the table.  The value will increment with every row inserted giving you the exact order that the rows were inserted.  Picking off the last one is a piece of cake.


Good Luck,
Kent
0
 
LeogalAuthor Commented:
i cannot add Identity to the file, would RRN be useable.
0
 
tliottaCommented:
In the attached sample the first data row (line 2) is what I am getting in my final results.
What "final results"? The final result of the example code is a SELECT COUNT(*)... The spreadsheet doesn't seem to match with AGNITEMALLOCATIONKEY.

...would RRN be useable.
Usable for what?

In general, if you are using SQL DDL-defined tables, then RRN is not reliable. It's 'possible' to create SQL tables and then alter them to make RRN reliable as an arrival-sequence indicator, but it can negatively affect performance. If you want an arrival sequence and you want SQL, add a sequencing column.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
LeogalAuthor Commented:
Okay I am trying to new approach  

here is my new code.

delete from  VD_GPL.AGNITEMALLOCATIONKEY;


create table qtemp.items(
itemallocationid char(10) default ' '
,itemId char(20) default ' '
,displayproductnumber char(30) default ' '
,INVENTDIM_CONFIGID char(15) default ' '
,dataareaid char(4) default ' ' 
,locationdescription char(30) default' '
,toteinsertid char(10) default '  '
,totesid char(10) default ' '
,unitpertote integer default 0
,configid char(15) default ' '
,inventsiteid char(10) default ' '
,somiitemid char(20) default ' '
,imprfg char(3) default ' '
,rtrcode char(9) default ' ' 
,imsts char(1) default ' ' 
,imtrnr char(10) default ' '
,lmpn char(15) default ' '
,lmcode char(9) default ' '
,lmco decimal(3) default 0
,lmsdes char(10) default ' '
);

update qtemp.items
set locationdescription =
          (SELECT    
           (CASE
           		WHEN LMSDES > ''
           		THEN SUBSTR(AX_DESC,1,5)||':'||LMSDES
          		 ELSE SUBSTR(AX_DESC,1,30)
       			END )
              from  ax_dat.dmfprod a
              inner join KBM400MFG.FKitmstr b on trim(a.displayproductnumber) = b.impn
              inner join KBM400MFG.FKLOCMST c on trim(a.displayproductnumber) = c.lmpn
              left join ax_dat.ax_short_desc d on d.kbm_desc = b.imtrnr 
               WHERE  impn = trim(a.displayproductnumber) and b.imtrnr = d.KBM_DESC);
 

insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,locationdescription )

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID,locationdescription
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)
         where imsts not in ('D','4') and imco = (1) AND lmco = (1) and 
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco,lmsdes)

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,lmco,lmsdes
from AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco = (3) AND lmco = (3) and 
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID  AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID)
);

insert into qtemp.items 
(
ITEMID, INVENTDIM_CONFIGID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco,lmsdes)

(Select DISTINCT ITEMID,INVENTDIM_CONFIGID,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,lmco,lmsdes
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber) 
         where imsts not in ('D','4') and imco = (5)  AND  lmco = (5) and
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID )
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,lmco,lmsdes)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco,lmsdes
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber) 
         where imsts not in ('D','4') and imco = (8)  AND lmco = (8) and
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID )
);

update qtemp.items
set locationdescription =
          (SELECT    
            (select disinct 
               (CASE
           		WHEN LMSDES > ''
           		THEN SUBSTR(AX_DESC,1,5)||':'||LMSDES
          		 ELSE SUBSTR(AX_DESC,1,30)
       			END 
       			 )
       		    
              from  ax_dat.dmfprod a
              inner join KBM400MFG.FKitmstr b on trim(a.displayproductnumber) = b.impn
              inner join KBM400MFG.FKLOCMST c on trim(a.displayproductnumber) = c.lmpn
              left join ax_dat.ax_short_desc d on d.kbm_desc = b.imtrnr 
               WHERE  impn = trim(a.displayproductnumber) and b.imtrnr = d.KBM_DESC
               )
            );
 
update qtemp.items
 set TOTEINSERTID = (Select PFPITP from KBM400CSTM.IMF929V where pfpn = trim(displayproductnumber)); 
 
update qtemp.items
 set TOTESID = (Select PFBETY from KBM400CSTM.IMF929V where pfpn = trim(displayproductnumber));
 
update qtemp.items
 set UNITPERTOTE = (Select PFSCQT from KBM400CSTM.IMF929V where pfpn = trim(displayproductnumber));					
					
Insert Into VD_GPL.AGNITEMALLOCATIONKEY
(InventDimid
,ItemallocationId
,Itemid
,LocationDescription
,toteinsertid
,totesid
,unitpertote
,configid
,inventsiteid
,SomiItemId
)
(
Select 
INVENTDIM_CONFIGID
,itemallocationid
,Itemid
,IfNull(locationdescription, ' ')
,IfNull(toteinsertid, ' ')
,IfNull(totesid, ' ')
,IfNull(unitpertote, 0)
,configid
,inventsiteid
,IfNull(SomiItemId,' ')
from qtemp.items);

Delete from  VD_GPL.AGNITEMALLOCATIONKEY  a where RRN(a) > (select MIN(RRN(b)) from VD_GPL.AGNITEMALLOCATIONKEY  b where  a.Itemid = b.Itemid  );
select  PURCHID , count(*) from VD_GPL.DMFPURCHTABLEENTITY  group by  PURCHID  having count(*) > 1;
 

SELECT COUNT (*) FROM VD_GPL.AGNITEMALLOCATIONKEY;
					  
select COUNT(*) from qtemp.items;
select * from VD_GPL.AGNITEMALLOCATIONKEY where itemid = '4270107286';
select * from qtemp.items;

Open in new window


the code within the subselect that is used to update the LocationDescription works fine outside of a subselect.  It does exactly what I need and returns the correct data.    As the code stands currently, it is generating this error

update qtemp.items
set locationdescription =
          (SELECT    
            (select disinct 
               (CASE
           		WHEN LMSDES > ''
           		THEN SUBSTR(AX_DESC,1,5)||':'||LMSDES
          		 ELSE SUBSTR(AX_DESC,1,30)
       			END 
       			 )
       		    
              from  ax_dat.dmfprod a
              inner join KBM400MFG.FKitmstr b on trim(a.displayproductnumber) = b.impn
              inner join KBM400MFG.FKLOCMST c on trim(a.displayproductnumber) = c.lmpn
              left join ax_dat.ax_short_desc d on d.kbm_desc = b.imtrnr 
               WHERE  impn = trim(a.displayproductnumber) and b.imtrnr = d.KBM_DESC
               )
            )

[SQL0104] Token ) was not valid. Valid tokens: + - AS <IDENTIFIER>.

Open in new window


it appears that I am now this close to getting the results table that I need. Can someone help me resolve this error?  I am struggling with getting a unique result for each returned row. If I use MIN it gives the same location description for each row, same is true with MAX; so I went   a select distinct...
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
It looks like your outer SELECT (the one in upper-case) doesn't have a corresponding FROM.
0
 
LeogalAuthor Commented:
@daveslash

I tried this code
update qtemp.items
set locationdescription =
          (SELECT    *
              (select disinct 
                  CASE
           		   WHEN LMSDES > ''
           		    THEN SUBSTR(AX_DESC,1,5)||':'||LMSDES
          		   ELSE SUBSTR(AX_DESC,1,30)
       			 END 
       			 )   
                       from  ax_dat.dmfprod a
              inner join KBM400MFG.FKitmstr b on trim(a.displayproductnumber) = b.impn
              inner join KBM400MFG.FKLOCMST c on trim(a.displayproductnumber) = c.lmpn
              left join ax_dat.ax_short_desc d on d.kbm_desc = b.imtrnr 
               WHERE  impn = trim(a.displayproductnumber) and b.imtrnr = d.KBM_DESC);

Open in new window


and got this error

[SQL0104] Token ( was not valid. Valid tokens: FROM INTO.

I am still learning SQL and have been teaching myself. Please excuse my ignorance. What is the correct way to write  this?  I am trying to get the concatenated result populated into the location description.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Greetings, Leogal.

In DB2, every SELECT must have a corresponding FROM. Notice that the first SELECT in your UPDATE statement has no corresponding FROM.
0
 
LeogalAuthor Commented:
@DaveSlash

I replaced my locationdescription update with this code:

update qtemp.items a
set locationdescription =
 
              (select disinct 
                   (CASE
           		    WHEN (select LMSDES from kbm400mfg.fklocmst ) > ''
           		       THEN (select SUBSTR(AX_DESC,1,5) from kbm400.fklocmst b where a.itemid = b.lmpn) ||':'||(select LMSDES from kbm400.fklocmst b where a.itemid = b.lmpn)
          		   ELSE (select SUBSTR(AX_DESC,1,5) from kbm400.fklocmst b where a.itemid = b.lmpn) 
                    END)
                      
               );

Open in new window


now I get this error:  

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ) was not valid. Valid tokens: + - AS <IDENTIFIER>. Cause . . . . . :   A syntax error was detected at token ).  Token ) is not a valid token.  A partial list of valid tokens is + - AS <IDENTIFIER>.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token ). Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

I am sure I am staring right at the problem but not seeing it can you help ?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Greetings, Leogal.

1."distinct" is spelled incorrectly
2. again, the very first SELECT has no FROM ... that most assuredly won't work

HTH,
DaveSlash

p.s. you would do well to download a good text-editor that does automatic matching of parentheses. Personally, I use Notepad++ (because it's free ... and I'm cheap), but I've also used UltaEdit with good results.
0
 
tliottaCommented:
As DaveSlash has said three times, every SELECT must have a FROM. That is, if you have a SELECT that is bounded by parentheses, there will also be a FROM clause inside the same parentheses. If SQL runs into a closing ")" before finding the matching FROM, it's going to throw an error saying that the ")" was found in the wrong place.

The structure will be something like this :

( SELECT ... FROM <tablename> )

or maybe like this:

( SELECT ... FROM <tablename> WHERE ... )

Open in new window


The FROM clause tells what the data is being selected from. Other clauses like WHERE might be added, but FROM must always appear after a SELECT list of columns.

Tom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now