May 20, 2014 at 9:51 am
Hi !
I'm converting access query to SQL Server
I have 7 reports in access
report #1 use a query A
report #2 use a query B that use date from query A
report #3 use a query C that use data from query B that use information from query A
report #4 use a query D that use data from query C on and so on ... I've around 7 query like that sharing data.
report #5...
report #6...
report #7...
Every query are pretty long have many join and case
What's the best way to share data between each query
Use an Inline table value function or Stored Procedure +temp table
I can't use a view because I need to have some parameters.
The problem with temp table is that I've around 30-50 fields on each query, pretty long to type and create tables.
thanks
May 20, 2014 at 9:56 am
dquirion78 (5/20/2014)
Hi !I'm converting access query to SQL Server
I have 7 reports in access
report #1 use a query A
report #2 use a query B that use date from query A
report #3 use a query C that use data from query B that use information from query A
report #4 use a query D that use data from query C on and so on ... I've around 7 query like that sharing data.
report #5...
report #6...
report #7...
Every query are pretty long have many join and case
What's the best way to share data between each query
Use an Inline table value function or Stored Procedure +temp table
I can't use a view because I need to have some parameters.
The problem with temp table is that I've around 30-50 fields on each query, pretty long to type and create tables.
thanks
NEVER base your development decisions on how many keystrokes it takes to create it. Base your decisions instead on what is best for the process at hand and the system in general. In this case it is impossible to say with certainty what is best because we have nowhere near enough information or knowledge of what you are doing. If you chose ITVF make certain they are single line and not multiple statements (MSTVF). The latter will perform horribly under many conditions, often times far worse than a scalar function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2014 at 11:25 am
Sorry it's in a query in french but you got the idea.
here an inline table value function.
basically I check purcharse order, after I check product finish, after product half finish, after I check Raw materials, ...
I removed half on fields I need to keep simple for you.
Here one these first steps. It will be also many CTE inside each table value function /stored procedure.
alter FUNCTION CeduleCour()
RETURNS TABLE
AS
RETURN
(
SELECT distinct ClientV2Standard.Client
,case when collectionventure = 'true' then cast(nomstyleventure AS NVARCHAR(50)) + case when nocouleurventure is null then '' else '-' + cast(nocouleurventure AS NVARCHAR(50)) end
else
nomstyleclient + '-' + cast(COALESCE(nomcouleurclient, ClientStyleCouleur.NOCOULEURCLIENT) AS NVARCHAR(50))
end as ItemclientCedule2
,IIf(ClientV2Standard.collectionventure = 'true', cast(StyleCollectionVenture.nomstyleventure AS NVARCHAR(50)) +
IIf(produit.nocouleurventure IS NULL, '', '-' + cast(produit.nocouleurventure AS NVARCHAR(50))), ClientStyle.nomstyleclient + '-' + cast(COALESCE(ClientStyleCouleur.nomcouleurclient, ClientStyleCouleur.NOCOULEURCLIENT) AS NVARCHAR(50))) AS ItemclientCedule
,cast(COALESCE(clientstylecouleur.nocouleurclient, clientstylecouleur.nomcouleurclient) AS NVARCHAR(50)) AS NomCoulClient
,IIf(ClientV2Standard.collectionventure = 'true', cast(IIf(ClientV2Standard.impressionstyleclient = 1, StyleCollectionVenture.nostyleventure, StyleCollectionVenture.nomstyleventure) AS NVARCHAR(50)) + IIf(produit.nocouleurventure IS NULL, '', '-' + cast(produit.nocouleurventure AS NVARCHAR(50))), IIf(NOT (clientstylecouleur.sku IS NULL), clientstylecouleur.sku, cast(IIf(clientv2standard.impressionstyleclient = 1, clientstyle.nostyleclient, clientstyle.nomstyleclient) AS NVARCHAR(50)) + IIf(ClientStyleCouleur.nocouleurclient IS NULL, '', '-' + cast(ClientStyleCouleur.NOCOULEURCLIENT AS NVARCHAR(50))))) AS Itemclient
,ClientStyle.NomStyleClient
,ClientV2Standard.ImpressionStyleClient
,IIf(ClientV2Standard.impressionstyleclient = 1, ClientStyle.nostyleclient, ClientStyle.nomstyleclient) AS StyleClient
FROM ClientV2Standard
INNER JOIN (
(
(
PRODUIT INNER JOIN ClientStyle ON PRODUIT.STYLE = ClientStyle.Style
) INNER JOIN ClientStyleCouleur ON (ClientStyle.NoClientStyleAuto = ClientStyleCouleur.NoClientStyle)
AND (PRODUIT.[COULEUR TAPIS] = ClientStyleCouleur.NoCouleur)
) LEFT JOIN StyleCollectionVenture ON PRODUIT.STYLE = StyleCollectionVenture.StyleTapis
) ON ClientV2Standard.NoClientAuto = ClientStyle.NoClient
)
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply