March 21, 2016 at 2:23 pm
I'm working on converting some MS Access queries into SQL Server equivalents. (Ultimately this is so that I can convert an Access report into a SSRS report.) Whoever the person is who wrote this stuff years ago divided his/her labor up. That makes sense to me. He/she wrote some simple queries, then other queries that depended upon the simple ones, etc. Right now I'm working on converting a queries that calls other queries to about 3 levels deep.
My initial approach has been to take any query which doesn't take any parameter, into a SQL view. This is still my approach.
But when I considered converting the Access queries that take parameters, I made a mistake. I thought all I'd have to do is convert them to stored procedures and I'd be done. So that's how I started. I didn't realize my mistake until about 20 minutes ago when I had to use the results of one of the stored procedures within another stored procedure's SELECT statement. Of course I knew you can't use a stored proc within a SELECT, but I proceeded as though you could until I hit the wall. I deserve that dent in my head.
(As an aside, I know I could use the EXEC with an INTO to put the table returned by a stored proc into a temporary table. But as I understand it I'd have to declare the definition of the temp table, which would make it susceptible to breaking, should the table that's returned by the stored proc change.)
So I started looking around for a way out of my predicament. I believe the right approach now is to do a user defined function. I've done so and it works fine, at least for simple SELECTs (e.g.: SELECT * from dbo.fnMyFunction('2016-3-21') )
But naturally the queries higher up are more complicated. It would be great if I could alias the function call so that I could use that alias within a higher level query. Let me give you an example of what I'm talking about. Here is one the MS Access query that's at a higher level within the call tree that whoever the original developer was, did:
SELECT vwPharmItems.DrugOrSupply, qryInvOutIssuesPostedBetwDates.InventoryActionID, qryInvOutIssuesPostedBetwDates.ItemPackageID,
vwPharmItems.OldItemNum, vwPharmItems.ItemDescr, Sum(qryInvOutIssuesPostedBetwDates.QtyToIssue) AS SumOfQtyIssuedShipped,
Sum(qryInvOutIssuesPostedBetwDates.QtyToIssue) AS SumOfQtyToIssue
FROM vwPharmItems
INNER JOIN qryInvOutIssuesPostedBetwDates ON vwPharmItems.ItemPackageID = qryInvOutIssuesPostedBetwDates.ItemPackageID
GROUP BY vwPharmItems.DrugOrSupply, qryInvOutIssuesPostedBetwDates.InventoryActionID, qryInvOutIssuesPostedBetwDates.ItemPackageID,
vwPharmItems.OldItemNum, vwPharmItems.ItemDescr
HAVING (((vwPharmItems.DrugOrSupply)=[Forms]![frmReports]![ItemTypeCombo]) AND ((qryInvOutIssuesPostedBetwDates.InventoryActionID)=2))
ORDER BY vwPharmItems.OldItemNum;
The problem is the query qryInvOutIssuesPostedBetwDates takes parameters (begin and end dates) so I've made it into a function which I call dbo.fnInvOutIssuesPostedBetwDates. But I would love to be able to alias that in the INNER JOIN statement. Only when I tried it on a simpler SELECT statement (you know, always try a simpler expression before you go off doing something more complex) it didn't work.
So I want to know, is it possible to alias a user defined function within a SELECT statement?
Kindest Regards, Rod Connect with me on LinkedIn.
March 21, 2016 at 3:42 pm
INNER JOIN dbo.fnMyFunction('2016-3-21') F ON F.ColumnName = OtherTable.ColumnName
dbo.fnMyFunction must be a table function to be used in a JOIN.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply