Leogal
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_N ame 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
I am using IBM data studio to pull data from a i5 v5r4
"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.
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:
I find that this kind of work-around often runs much faster:
HTH,
DaveSlash
select employeeid,
firstName,
lastName
from MySchema.employee e
where lastName = 'SMITH'
fetch first 1 row only
EMPLOYEEID FIRSTNAME LASTNAME
1,234 BOB SMITH
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
HTH,
DaveSlash
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.
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;
In the inner sub-query, be specific about which row you want.
becomes
or
(SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN
FETCH FIRST 1 ROW ONLY ) AS BOM_NAME ,
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 ,
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 ,
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.
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?
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 ,
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_
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER)
FETCH FIRST 1 ROW ONLY ) AS BOM_NAME ,
I am having difficulty understanding why i am getting these errors?
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;
Sorry....
Missing a parenthesis, but you figured that out.
(SELECT EcoResProductTranslation_N ame
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_N ame
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc )
Missing a parenthesis, but you figured that out.
(SELECT EcoResProductTranslation_N
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER)
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc )) AS BOM_NAME ,
Run just that query and see what's returned.
SELECT EcoResProductTranslation_N
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER)
where pspmrn = (select min(pspmrn) from kbm400mpg.fkpstruc )
ASKER
When I run the code below, it returns 0 records.
when I run it alone outside the select with the code like this
I get populated data.
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
SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc )
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
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.
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.
ASKER
this
00001
---------------
.D99.14237
this
returns this:
SELECT EcoResProductTranslation_N ame , displayproductnumber, pspmrn
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN
fetch first 1 rows only
ECORESPRODUCTTRANSLATION_N AME
-------------------------- ----
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_N AME DISPLAYPRODUCTNUMBER PSPMRN
-------------------------- ---- -------------------- ---------------
F-24-V-L US3 3' RHR LBR 712 /D .24.16169 .24.16169
select min(pspmrn) from kbm400mfg.fkpstruc
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
returns this:
SELECT EcoResProductTranslation_N
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER)
fetch first 1 rows only
ECORESPRODUCTTRANSLATION_N
--------------------------
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_N
--------------------------
F-24-V-L US3 3' RHR LBR 712 /D .24.16169 .24.16169
ASKER
when i use this code
i get this result set
ECORESPRODUCTTRANSLATION_N AME
-------------------------- ---
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
SELECT EcoResProductTranslation_Name
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on TRIM(DISPLAYPRODUCTNUMBER) = PSPMRN
where pspmrn = (select max(pspmrn) from kbm400mfg.fkpstruc )
i get this result set
ECORESPRODUCTTRANSLATION_N
--------------------------
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.
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_N ame
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on substring(DISPLAYPRODUCTNU MBER)1,15) = PSPMRN
where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc )
i am going to try this:
SELECT EcoResProductTranslation_N
FROM vd_gpl.DMFPRODUCTENTITY
INNER JOIN KBM400MFG.FKPSTRUC on substring(DISPLAYPRODUCTNU
where pspmrn = (select min(pspmrn) from kbm400mfg.fkpstruc )
ASKER
this
this
ECORESPRODUCTTRANSLATION_N AME 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
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 )
returns 0 records.this
SELECT EcoResProductTranslation_Name, SUBSTR(DISPLAYPRODUCTNUMBER,1,15)
FROM vd_gpl.DMFPRODUCTENTITY
FETCH FIRST 5 ROWS ONLY
returns this:ECORESPRODUCTTRANSLATION_N
--------------------------
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
ASKER
when i run this code
i get this error
[SQL0120] Use of function MIN not valid.
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 )
i get this error
[SQL0120] Use of function MIN not valid.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@leogal
Did you end up using a correlated subquery or a join? Thanks.
Did you end up using a correlated subquery or a join? Thanks.
HTH,
DaveSlash