Solved

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

Posted on 2015-01-22
22
815 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
ID: 40565496
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
ID: 40565536
"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
ID: 40565541
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
ID: 40565545
oops, sorry (a misread), got it.
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40565567
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
ID: 40565570
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
ID: 40565633
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
ID: 40565669
@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
ID: 40565705
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
ID: 40565710
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
ID: 40565715
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

Expert Comment

by:Kdo
ID: 40565719
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
ID: 40565727
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
ID: 40565738
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
ID: 40565755
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
ID: 40565763
@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
ID: 40565767
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
ID: 40565776
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
ID: 40565843
(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
ID: 40565845
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
ID: 40566330
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
ID: 40576090
@leogal

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
creating materialized view in ibm i series 5 68
SQL Server 2008 R2 - Execution Plan 3 57
Oracle query output question 4 36
Remove alpha from alphanumeric 4 60
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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

16 Experts available now in Live!

Get 1:1 Help Now