SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 OUTER APPLY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

quindraco
Starting Member

3 Posts

Posted - 01/07/2014 :  12:42:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 01/07/2014 :  13:37:46  Show Profile  Reply with Quote
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

Edited by - Lamprey on 01/07/2014 13:41:06
Go to Top of Page

quindraco
Starting Member

3 Posts

Posted - 01/07/2014 :  14:06:59  Show Profile  Reply with Quote
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.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
947 Posts

Posted - 01/07/2014 :  19:41:13  Show Profile  Reply with Quote
hi, can you post what is the error? tested and working fine to me...
Go to Top of Page

quindraco
Starting Member

3 Posts

Posted - 01/08/2014 :  08:40:24  Show Profile  Reply with Quote
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')

Edited by - quindraco on 01/08/2014 08:50:57
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
947 Posts

Posted - 01/08/2014 :  19:53:54  Show Profile  Reply with Quote
[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]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000