SQL Select with a function

Question

I have the following SQL Statement :

SELECT 
   RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))                 AS ItemNo,
   RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))                          AS Season, 
   ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')            AS Variant1, 
   ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')            AS Variant2,
   (SELECT * 
    FROM [dbo].[B2BGetSpringFinal] ( LAGKART.VARENUMMER, 
                                    LAGKART.VARIANT1,
                                    LAGKART.VARIANT2
                                  )) AS SpringAvailable
FROM 
   LAGKART

But I get this error :

Msg 170, Level 15, State 1, Line 8
Incorrect syntax near '.'.

But if I call the function with fixed values :

SELECT 
   RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))                 AS ItemNo,
   RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))                          AS Season, 
   ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')            AS Variant1, 
   ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')            AS Variant2,
   (SELECT * 
    FROM [dbo].[B2BGetSpringFinal] ( '6261', 
                                     'Black',
                                     'S'
                                   )) AS SpringAvailable
FROM 
   LAGKART

I get the desired result.

Any ideas?

Br Mads

what dialect is that? TSQL? it looks kind of whacky but not nuts enough to bt mysql. anway I'm pretty sure you can't pull result columns into subselects like that.either maybe you want a CTE? — Jasen, Dec 06 '14 at 07:42
It's T-SQL Think your are right that i can't use result columns in subselects. — Dauer, Dec 06 '14 at 07:46

score 0 · Answer 1 · answered Dec 06 '14 at 07:44

0

if your server supports CTEs you could try this one:

WITH a as (
SELECT VARENUMMER,
RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))                    AS ItemNo,
RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))                         AS Season, 
ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')           AS Variant1, 
ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')           AS Variant2,
FROM LAGKART
)
select *,
(SELECT * FROM [dbo].[B2BGetSpringFinal] ( a.VARENUMMER, 
                                           a.VARIANT1,
                                           a.VARIANT2
                                        ))                              AS SpringAvailable
from a ;

answered Dec 06 '14 at 07:44

Jasen

11,837
2
30
48

Thanks Jasen. Will give it a try later, and report back. – Dauer Dec 06 '14 at 07:51
I get the following error : Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'WITH' – Dauer Dec 06 '14 at 13:17
Add `;` before the `WITH` – gotqn Dec 06 '14 at 13:36
This won't work with SQL 2000 bc it is still passing query result column values as parameters into a table valued function which is not supported until SQL 2005. – BateTech Dec 06 '14 at 18:24

score 0 · Answer 2 · answered Dec 06 '14 at 10:05

0

You can use APPLY (CROSS or OUTER) to pass column(s) value(s) as arguments to a function:

SELECT RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))  AS ItemNo,
        RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),''))) AS Season, 
        ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'') AS Variant1, 
        ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'') AS Variant2,
        SpringAvailable.*
FROM LAGKART
CROSS APPLY 
(
    SELECT * 
    FROM [dbo].[B2BGetSpringFinal] ( LAGKART.VARENUMMER, LAGKART.VARIANT1,LAGKART.VARIANT2 )
) AS SpringAvailable

answered Dec 06 '14 at 10:05

gotqn

42,737
46
157
243

Thanks gtqn. Will give it a try later. – Dauer Dec 06 '14 at 10:11
I get the following error : Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'APPLY'. – Dauer Dec 06 '14 at 13:15
Is is an old SQL version (SQL 2000), so that might explain it. – Dauer Dec 06 '14 at 14:01

score 0 · Accepted Answer · answered Dec 06 '14 at 15:47

0

In SQL Server 2000, Only constants and @local_variables can be passed to table-valued functions. In SQL 2005 and greater this was fixed. You could try using a scalar function to get the SpringAvailable column value instead, or look at upgrading to a newer SQL Server version.

answered Dec 06 '14 at 15:47

BateTech

5,780
3
20
31

I can go from SQL 2000 to SQL Express 2005 for free, so that will be the soloution. Thanks! – Dauer Dec 06 '14 at 18:44

SQL Select with a function

3 Answers3