I've written several SQL Server user functions that simplify retrieval of data in complex queries/views. SQL Server allows functions to be referenced in a query. However, I can't drag them into the query diagram. I found an old knowledgebase article that describes how someone was doing that. I'm wondering if this feature was removed, broken or I just don't know how to do it?
I would be very interested in seeing the KB article, if you have the link. Can you please share with the rest of the forum readers?
To my knowledge, TDP has never had the ability to drag a function (e.g. from the Code Snippets View panel) to any part of a query diagram in the Visual Query Builder.
However, you can drag a code snippet/s into the SQL code that the Query Builder generates (via its Query tab), if that helps.
@Gary.Jerep it appears that you are correct. I thought that I reviewed a KB article that alluded to using a function in a query diagram. But now I cannot find it. So, I'll go with your answer, it's not a feature.
I see that I am able to drag the function to the Query tab. However, I'm not sure how to determine where to include it in the query. The function accepts an input parameter that is a unique identifier. The function performs some calculations and returns a one row table that contains four columns. One of those columns is named PRI which is the column that I would like to use in the query.
Currently the query performs the same calculation as the function and returns PRI as one of the columns in the SELECT ... PRI .... FROM..... Can you help me determine where and how to include the function?
SELECT MS.StandID,
MS.StandPosition AS POS,
MS.WtWarning AS Warning,
RW_UOM.UOMName AS UOM,
RS.RollSet,
RS.Section,
RS.SetNum,
RSS.StatusName AS Status,
RSS.AutoSelect AS [Auto],
RSS.InstallRollSet AS Inst,
RS.InService,
RS.OutService,
SEL_PRI.Effective,
RSSPriority.Priority,
RS.RS_UID,
MSR.MSRollSet_UID
FROM GSL_PI_DEV.PISystem.vRollSetsAll vRollSetsAll
CROSS JOIN
(
(
(
(
(
GSL_PI_DEV.PISystem.MillStandsRollsets MSR
INNER JOIN GSL_PI_DEV.PISystem.RollSets RS
ON (MSR.RS_UID = RS.RS_UID))
INNER JOIN
GSL_PI_DEV.PISystem.RollSetSelPriority RSSPriority <<=== this is part of the logic that is similar to the function
ON (RSSPriority.MSRollSet_UID = MSR.MSRollSet_UID))
RIGHT OUTER JOIN GSL_PI_DEV.PISystem.MillStands MS
ON (MSR.MS_UID = MS.MS_UID))
INNER JOIN GSL_PI_DEV.PISystem.RolledWtUOM RW_UOM
ON (MS.UOM_UID = RW_UOM.UOM_UID))
INNER JOIN
(SELECT RSSP.MSRollSet_UID, MAX (RSSP_AVAIL.EffDate) AS Effective
FROM GSL_PI_DEV.PISystem.RollSetSelPriority RSSP
INNER JOIN
(SELECT RSSP_CUR.RSSPRI_UID, <<=== this is also some of the logic
RSSP_CUR.MSRollSet_UID,
RSSP_CUR.EffDate,
RSSP_CUR.Priority
FROM GSL_PI_DEV.PISystem.RollSetSelPriority RSSP_CUR
WHERE (RSSP_CUR.EffDate <= SYSDATETIME ())) RSSP_AVAIL
ON (RSSP.MSRollSet_UID = RSSP_AVAIL.MSRollSet_UID)
GROUP BY RSSP.MSRollSet_UID) SEL_PRI
ON (MSR.MSRollSet_UID = SEL_PRI.MSRollSet_UID)
AND (SEL_PRI.Effective = RSSPriority.EffDate))
INNER JOIN GSL_PI_DEV.PISystem.RollSetStatus RSS
ON (RS.RSStatus_UID = RSS.RSStatus_UID)
Not sure if I understand what you're trying to do completely, and I'm personally not a SQL Server power expert, but if your table-valued function (sounds like) takes a parameter, then you'll need to either introduce a bind variable in the script executing your parent query, or embed the call/s in a procedure.
Might want to post your question on a forum like
or the like.
The other option possibly is to use the Visual Query Builder in TDP to join the underlying tables together, and then save the Query Builder file once the query logic tests ok so you don't have to keep building the query over and over.
Gary,
Thank you for the tips!