OUTER APPLY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OUTER APPLY

Author

Topic

quindraco
Starting Member

3 Posts

Posted - 2014-01-07 : 12:42:11

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, where I was unable to find any assistance, so here is a link to the relevant code:

http://stackoverflow.com/questions/20956777/correct-way-to-pass-table-valued-function-outputs-to-table-valued-functions-in-s

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working. Does anyone know how to do this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-07 : 13:37:46

Maybe if you post some sample data in a consumable format to show the issue we can help. I put together my own sample data and those functions worked just fine for me.

Also, are all the columns, specifically, Page NVARCHAR columns? Maybe you need to CAST/CONVERT them to NVARCHAR in the predicate?
i.e.: AND CAST(i.Page AS NVARCHAR(MAX)) like @page

Here are some links that might be helpful for putting together your sample data:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

quindraco
Starting Member

3 Posts

Posted - 2014-01-07 : 14:06:59

I had the same thought - the columns are nchar(10)s in their base table. CASTing them as you suggested has no effect, but I should probably do it anyway so people helping me are immediately aware it isn't the issue.

I'll put together some sample data and report back.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 19:41:13

hi, can you post what is the error? tested and working fine to me...

quindraco
Starting Member

3 Posts

Posted - 2014-01-08 : 08:40:24

WARNING: The code below constructs the necessary tables, views, and functions to demonstrate the problem by dropping them first if they exist, so please please please check first to make sure you don't drop anything of your own! The final three SELECTS demonstrate the problem; the final two SELECTS should have identical output, but do not - the first one (of the final two, so the middle of the three) is a three row table of strings, and the final one is a one row table containing only a NULL.

USE [TOM_GIS]
GO
IF OBJECT_ID(N'[dbo].[constant]', N'U') IS NOT NULL
DROP TABLE [dbo].[constant]
CREATE TABLE [dbo].[constant]
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BOOK varchar(5),
PAGE varchar(5),
DocID numeric(8, 0)
)
INSERT INTO [dbo].[constant]
VALUES(' 4043',' 125', 576030)
GO
IF OBJECT_ID(N'[dbo].[images]', N'U') IS NOT NULL
DROP TABLE [dbo].[images]
CREATE TABLE [dbo].[images]
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DocID numeric(8, 0),
ImageID numeric(12,0)
)
INSERT INTO [dbo].[images] VALUES(576030, 1589666);
INSERT INTO [dbo].[images] VALUES(576030, 1589667);
INSERT INTO [dbo].[images] VALUES(576030, 1589668);
GO
IF OBJECT_ID(N'[dbo].[addressBookPage]', N'U') IS NOT NULL
DROP TABLE [dbo].[addressBookPage]
CREATE TABLE [dbo].[addressBookPage]
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PARCEL_ADDRESS nvarchar(50),
BOOK nchar(10),
PAGE nchar(10),
)
INSERT INTO [dbo].[addressBookPage]
VALUES('155 CENTER STREET','4043', '125')
GO
IF OBJECT_ID(N'[dbo].[vw_quindraco]') IS NOT NULL
DROP VIEW [dbo].[vw_quindraco]
GO
CREATE VIEW [dbo].[vw_quindraco]
AS
WITH files AS (SELECT RIGHT('00000000' + LTRIM(STR(c.DocID)), 8) AS PathInfo
,RIGHT('0000000000' + LTRIM(STR(i.ImageID)), 12) AS FileName
,ltrim(c.Book) as Book
,ltrim(c.Page) as Page
FROM [dbo].[constant] AS c INNER JOIN
[dbo].[images] AS i ON c.DocID = i.DocID)
SELECT 'Images/' + SUBSTRING(PathInfo, 1, 2) + '/' + SUBSTRING(PathInfo, 3, 2) + '/' + SUBSTRING(PathInfo, 5, 2)
+ '/' + RIGHT(PathInfo, 8) + '/' + FileName + '.tif' AS FullFileName
,Book
,Page
FROM files AS files_1
GO
IF OBJECT_ID(N'[dbo].[bookAndPageFromAddress]') IS NOT NULL
DROP FUNCTION [dbo].[bookAndPageFromAddress];
GO
CREATE FUNCTION [dbo].[bookAndPageFromAddress] (@address NVARCHAR(max))
RETURNS TABLE AS RETURN(
SELECT PARCEL_ADDRESS AS Address, Book, Page
FROM [dbo].[addressBookPage]
WHERE PARCEL_ADDRESS like '%' + @address + '%'
);
GO
IF OBJECT_ID(N'[dbo].[imageFileNameFromBookPage]') IS NOT NULL
DROP FUNCTION [dbo].[imageFileNameFromBookPage];
GO
CREATE FUNCTION [dbo].[imageFileNameFromBookPage] (@book nvarchar(max), @page nvarchar(max))
RETURNS TABLE AS RETURN(
SELECT i.FullFileName
FROM [dbo].[vw_quindraco] i
WHERE i.Book like @book
AND i.Page like @page
);
GO
IF OBJECT_ID(N'[dbo].[imageFileNameFromAddress]') IS NOT NULL
DROP FUNCTION [dbo].[imageFileNameFromAddress];
GO
CREATE FUNCTION [dbo].[imageFileNameFromAddress] (@address NVARCHAR(max))
RETURNS TABLE AS RETURN(
SELECT *
FROM [dbo].[bookAndPageFromAddress](@address) addresses
OUTER APPLY [dbo].[imageFileNameFromBookPage](addresses.Book, addresses.Page) foo
);
GO
SELECT Book,Page FROM [dbo].[bookAndPageFromAddress]('155 Center Street');
SELECT FullFileName FROM [dbo].[imageFileNameFromBookPage]('4043','125');
SELECT FullFileName FROM [dbo].[imageFileNameFromAddress]('155 Center Street')

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-08 : 19:53:54

[code]
IF OBJECT_ID(N'[dbo].[imageFileNameFromAddress]') IS NOT NULL
DROP FUNCTION [dbo].[imageFileNameFromAddress];
GO
CREATE FUNCTION [dbo].[imageFileNameFromAddress] (@address NVARCHAR(max))
RETURNS TABLE AS RETURN(
SELECT *
FROM [dbo].[bookAndPageFromAddress](@address) addresses
OUTER APPLY [dbo].[imageFileNameFromBookPage](RTRIM(addresses.Book), RTRIM(addresses.Page)) foo
);
GO
[code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources