Link to home
Start Free TrialLog in
Avatar of Leogal
LeogalFlag for United States of America

asked on

DB2, how do I FETCH 1 ROW from a table inside a subselect ?

I am getting the error : [SQL0199] Keyword FETCH not expected. Valid tokens: ) UNION EXCEPT. HERE IS THE SUBSELECT query: (SELECT EcoResProductTranslation_Name FROM RJERN.DMFPRODUCTENTITY INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN FETCH FIRST 1 ROW ONLY ) AS BOM_NAME ,

I am using IBM data studio to pull data from a i5 v5r4
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

I don't believe DB2 allows you to use "FETCH FIRST 1 ROW ONLY " inside of a subselect. Post your whole query, and we can help you re-write it.

HTH,
DaveSlash
"Fetch first n rows only" in a subquery was introduced at db2 v.8.1 I believe.

Do you know what the DB2 version is that you use?

Anyway, given that error you will need an alternative, so please provide the full query as requested above.
From his question, it appears that he's using DB2 for i (a.k.a. AS/400) at v5.4 (a.k.a. V5R4), which is several years old.
oops, sorry (a misread), got it.
BTW, In the past, I've noticed that using the "FETCH FIRST n ROWS" clause can have a very significant negative impact on the performance of my queries. Therefore, instead of the (more straightforward) syntax like this:

select employeeid,       
       firstName,        
       lastName          
  from MySchema.employee e
 where lastName = 'SMITH'
 fetch first 1 row only  

EMPLOYEEID   FIRSTNAME        LASTNAME
     1,234   BOB              SMITH   

Open in new window


I find that this kind of work-around often runs much faster:

with orderedList as (                      
   select employeeid,                      
          firstName,                       
          lastName,                        
          row_number() over () as rowNumber
     from MySchema.employee e               
    where lastName = 'SMITH')              
select employeeid,       
       firstName,        
       lastName          
  from orderedList ol                      
 where ol.rowNumber = 1                    

EMPLOYEEID   FIRSTNAME        LASTNAME
     1,234   BOB              SMITH   

Open in new window


HTH,
DaveSlash
Avatar of Leogal

ASKER

Greetings,

Here is the full query. Originally I was working on the SQL Server but the process time is way to long so I was asked to move it Iseries.

-- database to use

    -- table the results write to, this script as it stands runs one company at a time to change the company, 
    -- change the DMFBOMENTITY table name and the where where psco = ?

    --DELETE DMFBOMENTITY3;
    --INSERT INTO DMFBOMENTITY3


    SELECT
    
  -- APPROVAL INFORMATION
                      'YES' AS BOM_APPROVED,
                      ' ' AS BOM_APPROVERPARTYNAME,
                      '00005' AS BOM_APPROVERPARTYNUMBER,
    
	                  '00005' AS BOM_APPROVERPERSONNELNUMBER-- BOM INFORMATION & DESCRIPTION
                      ,
                      PSPMRN AS BOM_BOMID,
                      ' ' AS BOM_CHECKBOM,
                      'Prts' AS BOM_ITEMGROUPID,


		           (SELECT EcoResProductTranslation_Name 
     					 FROM vd_gpl.DMFPRODUCTENTITY  
        					INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
        					  FETCH FIRST 1 ROW ONLY ) AS  BOM_NAME  ,
						 
                      ' ' AS BOM_ROUTE_CONFIGGROUPID,
                      0 AS BOM_ROUTE_LINENUM,
                      
					  ' ' AS BOM_ROUTE_NAME,
                      
					  'Variable' AS BOMCONSUMP,----- ITEM QUANTITY
					   

					  PSQTY AS BOMQTY,
                      1 AS BOMQTYSERIE,----- BOM TYPE

                      CASE 
						WHEN Phantom IN ('N', 'P') THEN 'Phantom' 
						WHEN Phantom = ' ' THEN 'ITEM' ELSE 'ITEM' 
						END AS BOMTYPE, 


                      'YES' AS CALCULATION---CONFIGURATION INFO
                      ,
                      ' ' AS CONFIGGROUPID,

					  SUBSTRING(INVENTDIM_CONFIGID,1,10) as  CONFIGID


                      ,0 AS CONSTANT,
                      ' ' AS DEFINITIONGROUP,
                      0 AS DENSITY,
                      0 AS DEPTH,
                      ' ' AS ENDSCHEDCONSUMP,
                      ' ' AS EXECUTIONID,
                      ' ' AS FORMULA---  FROM DATE
                      ,
                      0 AS FROMDATE---- PRODUCT DIMENSIONS: NOT USED AT THIS TIME
                      ,
                      0 AS HEIGHT,
                      ' ' AS INVENTBATCHID,
                      ' ' AS INVENTCOLORID,
                      ' ' AS INVENTDIMID,
                      ' ' AS INVENTLOCATIONID,
                      ' ' AS INVENTSERIALID,
                      'IND' AS INVENTSITEID,
                      ' ' AS INVENTSIZEID,
                      ' ' AS INVENTSTYLEID,
                      0 AS ISSELECTED,

                      ' ' AS ITEMBOMID,--- ITEMID / CHILD PART #,
                      PSCMRN AS ITEMID,
 
                      ' ' AS ITEMPBAID,
                      ' ' AS ITEMROUTEID--- LINE NUMBER AUTOMATED AT THE SERVER LEVEL/ WE DO NOT USE PSOPR
                      ,
                      0 AS LINENUM,
                      ' ' AS OPRNUM,
                      ' ' AS PDSBASEVALUE,
                      0 AS PDSCWQTY,
                      ' ' AS PDSINGREDIENTTYPE,--- INHERITIED INFORMATION

                      ' ' AS PDSINHERITCOPRODUCTBATCHATTRIB,
                      ' ' AS PDSINHERITCOPRODUCTSHELFLIFE,
                      ' ' AS PDSINHERITENDITEMBATCHATTRIB,
                      ' ' AS PDSINHERITENDITEMSHELFLIFE,
                      0 AS PMFFORMULAPCT,
                      ' ' AS PMFPCTENABLE,
                      ' ' AS PMFPLANGROUPID,
                      0 AS PMFPLANGROUPPRIORITY,
                      ' ' AS PMFSCALABLE,--- ITEM SEQ #

                      --CAST (LTRIM(RTRIM(PSSEQ)) AS VARCHAR (20)) AS POSITION--- ITEM FINISH
                      ' '  AS POSITION,

                      'FINISH' AS PRODFLUSHINGPRINCIP,
                      ' ' AS PROJSETSUBPRODTOCONSUMED,
                      ' ' AS ROUNDUP,
                      0 AS ROUNDUPQTY,
                      0 AS ROWID,
                      0 AS SCRAPCONST,
                      0 AS SCRAPVAR--- TO DATE
                      ,

                      CASE 
						WHEN PSEXMO BETWEEN 1 AND 12 
						THEN    (PSEXYR * 10000) || (PSEXMO * 100) || PSEXDA  
						WHEN PSEXYR = 99 THEN '2049-12-31' 
						END AS TODATE,


                      0 AS TRANSFERSTATUS-- UNIT & VENDOR ID
                      ,
					
					 CASE 
						WHEN BOMUnitId <> ' '
                                AND BOMUnitId <> 'PC' THEN BOMUnitId ELSE 'EA' 
                      END AS UNITID,


                      '  ' AS VENDID,
                      0 AS WIDTH,
                      ' ' AS WMSLOCATIONID,
                      ' ' AS WMSPALLETID,
                      ' ' AS WRKCTRCONSUMPTION,
                      0 AS RECVERSION,
                      0 AS PARTITION,
                      0 AS RECID


    ---  ALL CHAINS BY CHILD ITEM# and Validate that co# is correct, pull on 2014 
    ---- and later years for expiration date (includes 99) 
    ---- and validate that the century PSEXCT is not 20th century (19xx)


    
			   FROM    vd_gpl.DMFPRODUCTENTITY b
				   inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
				     where c.psco = 3 and PSEXYR > 14  
				      FETCH FIRST 100 ROWS ONLY;

 

Open in new window

In the inner sub-query, be specific about which row you want.


(SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
FETCH FIRST 1 ROW ONLY ) AS  BOM_NAME  ,

Open in new window


becomes


(SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc ) AS  BOM_NAME  ,

Open in new window


or


(SELECT min (EcoResProductTranslation_Name)
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
FETCH FIRST 1 ROW ONLY ) AS  BOM_NAME  ,

Open in new window

Avatar of Leogal

ASKER

@KDO,

when i inserted the query subselect below into my main program i got this error

[SQL0104] Token , was not valid. Valid tokens: ) UNION EXCEPT.


(SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc ) AS  BOM_NAME  ,

Open in new window



when inserted the code below I had this error occur when the whole script ran:

[SQL0199] Keyword FETCH not expected. Valid tokens: ) UNION EXCEPT.


(SELECT min (EcoResProductTranslation_Name)
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
FETCH FIRST 1 ROW ONLY ) AS  BOM_NAME  ,

I am having difficulty understanding why i am getting these errors?
Avatar of Leogal

ASKER

here is the latest code, it runs without error.  The problem now is that the field BOM_NAM is now populating with NULL's?  Please help

-- database to use

    -- table the results write to, this script as it stands runs one company at a time to change the company, 
    -- change the DMFBOMENTITY table name and the where where psco = ?

    --DELETE DMFBOMENTITY3;
    --INSERT INTO DMFBOMENTITY3


    SELECT
    
  -- APPROVAL INFORMATION
                      'YES' AS BOM_APPROVED,
                      ' ' AS BOM_APPROVERPARTYNAME,
                      '00005' AS BOM_APPROVERPARTYNUMBER,
    
	                  '00005' AS BOM_APPROVERPERSONNELNUMBER-- BOM INFORMATION & DESCRIPTION
                      ,
                      PSPMRN AS BOM_BOMID,
                      ' ' AS BOM_CHECKBOM,
                      'Prts' AS BOM_ITEMGROUPID,

						(SELECT EcoResProductTranslation_Name 
							FROM vd_gpl.DMFPRODUCTENTITY  
								INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
							where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc ) )  AS  BOM_NAM, 
						 
                      ' ' AS BOM_ROUTE_CONFIGGROUPID,
                      0 AS BOM_ROUTE_LINENUM,
                      
					  ' ' AS BOM_ROUTE_NAME,
                      
					  'Variable' AS BOMCONSUMP,----- ITEM QUANTITY
					   

					  PSQTY AS BOMQTY,
                      1 AS BOMQTYSERIE,----- BOM TYPE

                      CASE 
						WHEN Phantom IN ('N', 'P') THEN 'Phantom' 
						WHEN Phantom = ' ' THEN 'ITEM' ELSE 'ITEM' 
						END AS BOMTYPE, 


                      'YES' AS CALCULATION---CONFIGURATION INFO
                      ,
                      ' ' AS CONFIGGROUPID,

					  SUBSTRING(INVENTDIM_CONFIGID,1,10) as  CONFIGID


                      ,0 AS CONSTANT,
                      ' ' AS DEFINITIONGROUP,
                      0 AS DENSITY,
                      0 AS DEPTH,
                      ' ' AS ENDSCHEDCONSUMP,
                      ' ' AS EXECUTIONID,
                      ' ' AS FORMULA---  FROM DATE
                      ,
                      0 AS FROMDATE---- PRODUCT DIMENSIONS: NOT USED AT THIS TIME
                      ,
                      0 AS HEIGHT,
                      ' ' AS INVENTBATCHID,
                      ' ' AS INVENTCOLORID,
                      ' ' AS INVENTDIMID,
                      ' ' AS INVENTLOCATIONID,
                      ' ' AS INVENTSERIALID,
                      'IND' AS INVENTSITEID,
                      ' ' AS INVENTSIZEID,
                      ' ' AS INVENTSTYLEID,
                      0 AS ISSELECTED,

                      ' ' AS ITEMBOMID,--- ITEMID / CHILD PART #,
                      PSCMRN AS ITEMID,
 
                      ' ' AS ITEMPBAID,
                      ' ' AS ITEMROUTEID--- LINE NUMBER AUTOMATED AT THE SERVER LEVEL/ WE DO NOT USE PSOPR
                      ,
                      0 AS LINENUM,
                      ' ' AS OPRNUM,
                      ' ' AS PDSBASEVALUE,
                      0 AS PDSCWQTY,
                      ' ' AS PDSINGREDIENTTYPE,--- INHERITIED INFORMATION

                      ' ' AS PDSINHERITCOPRODUCTBATCHATTRIB,
                      ' ' AS PDSINHERITCOPRODUCTSHELFLIFE,
                      ' ' AS PDSINHERITENDITEMBATCHATTRIB,
                      ' ' AS PDSINHERITENDITEMSHELFLIFE,
                      0 AS PMFFORMULAPCT,
                      ' ' AS PMFPCTENABLE,
                      ' ' AS PMFPLANGROUPID,
                      0 AS PMFPLANGROUPPRIORITY,
                      ' ' AS PMFSCALABLE,--- ITEM SEQ #

                      --CAST (LTRIM(RTRIM(PSSEQ)) AS VARCHAR (20)) AS POSITION--- ITEM FINISH
                      ' '  AS POSITION,

                      'FINISH' AS PRODFLUSHINGPRINCIP,
                      ' ' AS PROJSETSUBPRODTOCONSUMED,
                      ' ' AS ROUNDUP,
                      0 AS ROUNDUPQTY,
                      0 AS ROWID,
                      0 AS SCRAPCONST,
                      0 AS SCRAPVAR--- TO DATE
                      ,

                      CASE 
						WHEN PSEXMO BETWEEN 1 AND 12 
						THEN    (PSEXYR * 10000) || (PSEXMO * 100) || PSEXDA  
						WHEN PSEXYR = 99 THEN '2049-12-31' 
						END AS TODATE,


                      0 AS TRANSFERSTATUS-- UNIT & VENDOR ID
                      ,
					
					 CASE 
						WHEN BOMUnitId <> ' '
                                AND BOMUnitId <> 'PC' THEN BOMUnitId ELSE 'EA' 
                      END AS UNITID,


                      '  ' AS VENDID,
                      0 AS WIDTH,
                      ' ' AS WMSLOCATIONID,
                      ' ' AS WMSPALLETID,
                      ' ' AS WRKCTRCONSUMPTION,
                      0 AS RECVERSION,
                      0 AS PARTITION,
                      0 AS RECID


    ---  ALL CHAINS BY CHILD ITEM# and Validate that co# is correct, pull on 2014 
    ---- and later years for expiration date (includes 99) 
    ---- and validate that the century PSEXCT is not 20th century (19xx)


    
			   FROM    RJERN.DMFPRODUCTENTITY b
				   inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
				     where c.psco = 3 and PSEXYR > 14  
				      FETCH FIRST 100 ROWS ONLY;

 

Open in new window

Sorry....

Missing a parenthesis, but you figured that out.

(SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc )) AS  BOM_NAME  ,

Run just that query and see what's returned.

SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc )
Avatar of Leogal

ASKER

When I run the code below, it returns 0 records.

SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc ) 

Open in new window


when I run it alone outside the select with the code like this
I get populated data.

SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
 fetch first 10 rows only

Open in new window


When I run this code by itself, the return row has the correct data and it is only one row.

select min(pspmrn) from kbm400mfg.fkpstruc
Part of the challenge here is not knowing your database or data.  So I'm guessing a bit (just like the other guys did).

Change the min() function to max() and see what happens.  I'm wondering what the data would look like that the min() function returned nothing.
Avatar of Leogal

ASKER

this
select min(pspmrn) from kbm400mfg.fkpstruc

Open in new window

 returns this  

00001
 ---------------
 .D99.14237    

this
SELECT EcoResProductTranslation_Name 
  FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
 fetch first 5 rows only

Open in new window



returns this:

SELECT EcoResProductTranslation_Name , displayproductnumber, pspmrn
  FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
 fetch first 1 rows only

 ECORESPRODUCTTRANSLATION_NAME
 ------------------------------
 F-24-V-L US3 3' RHR LBR 712 /D
 AJK70 US3
 AJK70 US3
 AJK70 US3
 AJK70 US3

using this query you see the data being pulled in.


 ECORESPRODUCTTRANSLATION_NAME        DISPLAYPRODUCTNUMBER       PSPMRN
 ------------------------------                                       --------------------                       ---------------
 F-24-V-L US3 3' RHR LBR 712 /D                         .24.16169                          .24.16169
Avatar of Leogal

ASKER

when i use this code
SELECT EcoResProductTranslation_Name 
			FROM vd_gpl.DMFPRODUCTENTITY  
			INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
			where pspmrn = (select max(pspmrn) from kbm400mfg.fkpstruc )

Open in new window


i get this result set
 ECORESPRODUCTTRANSLATION_NAME
 -----------------------------
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
 ABRASIVE BELTS & WHEELS - POL
That's why the use of an aggregate function like min or max, to keep reduce the result to 1 row.
Avatar of Leogal

ASKER

@KDO, i am baffled why i am returning nulls instead of data. I understand that min is the way to go and the query solution itself looks good.  The field is varchar(60) , PSPMRN is varchar(15) . I do not know for the life of me why i did not see this early. I can only say that i was very frustrated with figuring out how to pull one row only.


i am going to try this:

SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY  
INNER JOIN KBM400MFG.FKPSTRUC on  substring(DISPLAYPRODUCTNUMBER)1,15) =  PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc )
Avatar of Leogal

ASKER

this
SELECT EcoResProductTranslation_Name 
	FROM vd_gpl.DMFPRODUCTENTITY  
	INNER JOIN KBM400MFG.FKPSTRUC on  SUBSTR(DISPLAYPRODUCTNUMBER,1,15) =  PSPMRN
	where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc )

Open in new window

returns 0 records.

this
	SELECT EcoResProductTranslation_Name, SUBSTR(DISPLAYPRODUCTNUMBER,1,15)
	FROM vd_gpl.DMFPRODUCTENTITY  
	FETCH FIRST 5 ROWS ONLY

Open in new window

returns this:

 ECORESPRODUCTTRANSLATION_NAME       00002
 -----------------------------       ---------------
 ELEC 9927 DEV - SHORT 42 FIN       E9927S-42      
 ELEC 9947 DEV - LONG 03 FIN        E9947L-03      
 ELEC 9947 DEV - LONG 06 FIN        E9947L-06      
 ELEC 9947 DEV - LONG 10 FIN        E9947L-10      
 ELEC 9947 DEV - LONG 18 FIN        E9947L-18      


its driving me crazy. I know it is something small, just can figure out what it is
Avatar of Leogal

ASKER

when i run this code
 
SELECT EcoResProductTranslation_Name 
FROM vd_gpl.DMFPRODUCTENTITY  x
INNER JOIN KBM400MFG.FKPSTRUC y on  TRIM(DISPLAYPRODUCTNUMBER) = y. PSPMRN
where y.pspmrn = (select min(y.pspmrn) from kbm400mfg.fkpstruc )

Open in new window


i get this error

[SQL0120] Use of function MIN not valid.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
one other comment: if possible avoid using functions on fields used in joins.

i.e. Is it REALLY necessary to use TRIM() to match DISPLAYPRODUCTNUMBER to PSPMRN ?

(we cannot tell if it is or isn't, I'm just asking because it should be faster if you don't need it)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@leogal

Did you end up using a correlated subquery or a join?  Thanks.