January 7, 2014 at 10:51 am
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:
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?
January 7, 2014 at 11:22 am
from the page you posted, someone suggested this code
SELECT *
FROM
[dbo].bookPageFromAddress(@address) addresses
outer apply [dbo].[imageFileFromBookPage](addresses.Book, addresses.Page) foo
you said it didn't work, but didn't say why, or whether you got an error or just unexpected results.
details would help here;
Lowell
January 8, 2014 at 6:42 am
I actually incorporated the code Lowell pointed out and edited the original post, although it was easy to miss; I have now instead re-built the entire post to try and be much clearer.
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.
Note that the OUTER APPLY logic Lowell asked about is fully incorporated, as it was in the original link when I posted it here.
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')
January 8, 2014 at 7:04 am
Your sample code generates errors:
Msg 208, Level 16, State 1, Procedure imageFileNameFromAddress, Line 5
Invalid object name 'dbo.imageFileFromBookPage'.
(1 row(s) affected)
(3 row(s) affected)
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.imageFileNameFromAddress'.
What's your objective here? I think it's something like "using a couple of variables as input parameters, get some rows & columns out of this bunch of tables using the variables as filters". If this is the case, show what output you expect to see from a set of parameters, and why.
I think you've overcomplicated this and you've ended up pulling yourself up by your shoelaces and falling over. A single view (or inline function) should be sufficient and will eliminate that embarrassing WTF moment down the line when someone else comes along to maintain your code.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 7:40 am
If you are still wondering why the last query in your script returns NULL for the fullfilename (or whatever), it's because you've been inconsistent with your datatypes.
-- Columns BOOK and PAGE in table [dbo].[addressBookPage] are defined as nchar(10)
-- and VARCHAR(5) in table [dbo].[constant]
-- look at the values when you bound them in square brackets
SELECT
'['+a.Book+']', -- [4043 ]
'['+a.[Page]+']', -- [125 ]
foo.*
FROM [dbo].[addressBookPage] a
outer APPLY [dbo].[imageFileNameFromBookPage](a.Book, a.[Page]) foo
WHERE a.PARCEL_ADDRESS like '%' + '155 Center Street' + '%'
-- now look at FUNCTION [dbo].[imageFileNameFromBookPage] where htey are passed in as VARCHAR(MAX)
WHERE i.Book LIKE @book
AND i.Page LIKE @page
-- LIKE can't match [4043 ] to [4043] without either trimming or using a wildcard
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 7:44 am
ChrisM@Work (1/8/2014)
Your sample code generates errors:
Msg 208, Level 16, State 1, Procedure imageFileNameFromAddress, Line 5Invalid object name 'dbo.imageFileFromBookPage'.
(1 row(s) affected)
(3 row(s) affected)
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.imageFileNameFromAddress'.
You used a mispasted version which I corrected seconds later - you're too fast for me! Current version does not do that.
What's your objective here?
Dammit, I suspected stating it only three ways was not enough. How can I be clearer? I will try pseudocode.
I want to know how to properly write this function in SQL:
//Note: func1 and func2 return tables as output and take scalars as input
def compose(func1, func2, arguments_to_func1){
return_table = new table;
for row in func1(arguments_to_func1){
return_table.appendTable(func2(rowToArguments(row)));
}
return return_table;
}
I think it's something like "using a couple of variables as input parameters, get some rows & columns out of this bunch of tables using the variables as filters".[/quote[
"Using an arbitrary number of variables as input parameters, compose functions together such that they can be called on each other."
If this is the case, show what output you expect to see from a set of parameters, and why.
Input is '155 Center Street'; correct outputs to the three SELECTs follow.
First SELECT:
Book Page
---------- ----------
4043 125
Second and Third:
FullFileName
-----------------------------------------
Images/00/57/60/00576030/000001589666.tif
Images/00/57/60/00576030/000001589667.tif
Images/00/57/60/00576030/000001589668.tif
Incorrect output third actually produces:
FullFileName
-----------------------------------------
NULL
I think you've overcomplicated this and you've ended up pulling yourself up by your shoelaces and falling over. A single view (or inline function) should be sufficient and will eliminate that embarrassing WTF moment down the line when someone else comes along to maintain your code.
I am not familiar with parametrized views, nor do I know how to call them, would be very grateful if you would teach me how - I would prefer a view to a function here. My current solution is a user-defined in-line table-valued function which does not, as I stated, actually work.
January 8, 2014 at 7:49 am
Is [address] the only filter/parameter you are intending to pass in?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 7:52 am
Thank you! You've finally sussed it. I went back to my function and modified it to rtrim the select from the table with the mangled data type and it works a treat.
January 8, 2014 at 9:27 am
quindraco (1/8/2014)
Thank you! You've finally sussed it. I went back to my function and modified it to rtrim the select from the table with the mangled data type and it works a treat.
quindraco drives slowly into a garage.
quindraco: “My car pulls to one side when I’m driving“
Mechanic: “You’ve got a flat tyre”
quindraco: “Oh right! I’ll flatten the tyre on the opposite side then. It will even everything out”
Why not make the data type consistent for [book] and [page] columns (and variables to hold the values)? Fix it properly now and it's likely to save you a whole world of pain in the future.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply