Solved

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

Posted on 2015-01-22
22
770 Views
Last Modified: 2015-01-28
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
0
Comment
Question by:Leogal
  • 9
  • 5
  • 5
  • +1
22 Comments
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"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.
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
oops, sorry (a misread), got it.
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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
0
 

Author Comment

by:Leogal
Comment Utility
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

0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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

0
 

Author Comment

by:Leogal
Comment Utility
@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?
0
 

Author Comment

by:Leogal
Comment Utility
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

0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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 )
0
 

Author Comment

by:Leogal
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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.
0
 

Author Comment

by:Leogal
Comment Utility
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
0
 

Author Comment

by:Leogal
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
That's why the use of an aggregate function like min or max, to keep reduce the result to 1 row.
0
 

Author Comment

by:Leogal
Comment Utility
@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 )
0
 

Author Comment

by:Leogal
Comment Utility
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
0
 

Author Comment

by:Leogal
Comment Utility
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
Comment Utility
(SELECT EcoResProductTranslation_Name
             FROM vd_gpl.DMFPRODUCTENTITY  
                INNER JOIN KBM400MFG.FKPSTRUC on  TRIM(DISPLAYPRODUCTNUMBER) =  PSPMRN
                  FETCH FIRST 1 ROW ONLY ) AS  BOM_NAME  ,

Sorry to chide, but it is really (REALLY)  important that you prefix fields with the table (or table alias if being used). At the moment I'm not sure where each field comes from in the subquery hence I have to guess some of this.

I assume the wanted name field comes from the joined table
 i.e. KBM400MFG.FKPSTRUC.EcoResProductTranslation_Name

If this assumption is invalid the rest of this comment may be invalid.

------------
A problem with the existing subquery is that you are already inside vd_gpl.DMFPRODUCTENTITY
(line 139; " FROM    vd_gpl.DMFPRODUCTENTITY b")
so you don't have to recall that table.

Another issue is that this type of query should be "correlated". i.e. it should be relevant to the current row of the resultset which is typically handled by passing current row values into the where clause of the subquery - which I don't see here. Note: correlation can have the effect of reducing the number of rows returned by a subquery too.

Perhaps you could try this as a substitute for your existing subquery:
(
   select max(c.EcoResProductTranslation_Name) from KBM400MFG.FKPSTRUC c
   where TRIM(b.DISPLAYPRODUCTNUMBER) = c.PSPMRN
 ) AS  BOM_NAME

Here we are passing the current row value for DISPLAYPRODUCTNUMBER into a where clause against a single table for the wanted name field.

---------------

An alternate (and usually more efficient) method.

I dislike using "correlated subqueries" in a select clause as they are often a cause of poor performance. Not always, but often. So, an alternative is to JOIN a table (or subquery)

e.g.
   FROM    vd_gpl.DMFPRODUCTENTITY b
       inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
      left join KBM400MFG.FKPSTRUC d on TRIM(b.DISPLAYPRODUCTNUMBER) = c.PSPMRN
         where c.psco = 3 and PSEXYR > 14  

Now, without the benefit of access to tables and data, that suggestion might produce too many rows, which you can test by comparing these:
select count(*)
   FROM    vd_gpl.DMFPRODUCTENTITY b
       inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
         where c.psco = 3 and PSEXYR > 14  
;

select count(*)
   FROM    vd_gpl.DMFPRODUCTENTITY b
       inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
       left join KBM400MFG.FKPSTRUC d on TRIM(b.DISPLAYPRODUCTNUMBER) = c.PSPMRN
         where c.psco = 3 and PSEXYR > 14  
;

If there are too many rows then you might be able to use a "derived table" instead. e.g.

    FROM    vd_gpl.DMFPRODUCTENTITY b
       inner join KBM400MFG.FKPSTRUC c on b.DISPLAYPRODUCTNUMBER = c.PSCMRN
        left join (
                     select PSPMRN,  max(EcoResProductTranslation_Name) as BOM_NAME
                     from KBM400MFG.FKPSTRUC
                     group by PSPMRN
                  ) c on TRIM(b.DISPLAYPRODUCTNUMBER) = c.PSPMRN

and now all you need to do in your select clause is include c.bom_name
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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)
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
Comment Utility
I must have had a brain cramp.  Apologies.

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 )

I assumed that this sub-query would be returning several columns.  Since you're only returning the value in a single column this can be simplified to:

SELECT min(EcoResProductTranslation_Name)
      FROM vd_gpl.DMFPRODUCTENTITY  
      INNER JOIN KBM400MFG.FKPSTRUC on  SUBSTR(DISPLAYPRODUCTNUMBER,1,15) =  PSPMRN

Hopefully, the inner join results in only a few rows so that performance isn't an issue.

Kent
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@leogal

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now