Wrong record extracting - DB2 SQL

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

i cannot add Identity to the file, would RRN be useable.

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.

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;

Select allOpen 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>.

Select allOpen 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...

It looks like your outer SELECT (the one in upper-case) doesn't have a corresponding FROM.

@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);

Select allOpen 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.

@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)
                      
               );

Select allOpen 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 ?

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.