0

I have this SELECT query I need to convert to a T-SQL view:

SELECT 
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação, 
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe, 
    T_ComprasDet.Id_Tamanho, 
    SUM([Peso Kg] * [Caixas]) AS Peso, 
    T_ComprasDet.Id_Frescura, Q_Guia_Fact.Valor
FROM 
    (T_Compras 
LEFT JOIN 
    Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia) 
INNER JOIN 
    T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
GROUP BY 
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação, 
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe, 
    T_ComprasDet.Id_Tamanho, T_ComprasDet.Id_Frescura, 
    Q_Guia_Fact.Valor
HAVING 
    (((T_Compras.Guia) = [Forms]![F_Compras]![Guia]));

I am able to get everything working but I don't know how that last HAVING clause would be in T-SQL. In Access what it is doing is prompting the user for a guide number (guide="guia") so that it only shows rows with that guide number.

Thanks in advance!

| improve this question | | | | |
  • In T-SQL, you would typically specify a parameter passed by the calling application: HAVING T_Compras.Guia=@Guia. Unlike Access, T-SQL does not provide a UI to prompt users for run-time values. – Dan Guzman Aug 6 '15 at 1:06
  • SQL Server won't let me save the view without specifying a parameter value but the idea isn't to have a query for every value 'Guia' can assume,l since it will keep growing. Do you know how I could make a view so that it would filter results based on a number the user inputs, not necessarily in the same way it was originally but in a for for example? – António Bezerra Aug 6 '15 at 10:57
0

Create the view without the HAVING clause, and when you query the view, filter it using:

WHERE Guia = ????

...with your "????" being the value that you're passing in from your form.

| improve this answer | | | | |
  • I'm asking you the same question I asked Dan. Do you know how I could make a view so that it would filter results based on a number the user inputs, not necessarily in the same way it was originally but in a for for example? The solution you posted seems to require me to edit the value in the SQL code itself, which I don't want to do. – António Bezerra Aug 6 '15 at 10:59
  • A view doesn't store data. When you use the view, providing the value for Guia when you do, it runs the query, using the value as if it's in the HAVING clause. – Rob Farley Aug 6 '15 at 11:02
  • But do I need to input the value directly in the SQL code or can it get it from a text box in a form, for example? – António Bezerra Aug 6 '15 at 11:07
  • You create the view WITHOUT the HAVING clause. But then you use the view as if it's a table, specifying the value in the WHERE clause, passing it in from your form like you would for any table. – Rob Farley Aug 6 '15 at 11:10
  • Oh, ok. Still, I'm very new to this so how would I pass the value for the WHERE clause using the form? Sorry if this is a dumb question. – António Bezerra Aug 6 '15 at 11:43
0

A view cannot specify parameters so you need to filter when selecting from the view using a WHERE clause like Rob mentioned:

CREATE VIEW dbo.View_Example
AS
SELECT  T_Compras.Guia
      , T_Compras.Data
      , T_Compras.Id_Embarcação
      , T_Compras.[Nº Maré]
      , T_ComprasDet.Id_Peixe
      , T_ComprasDet.Id_Tamanho
      , SUM([Peso Kg] * [Caixas]) AS Peso
      , T_ComprasDet.Id_Frescura
      , Q_Guia_Fact.Valor
FROM    T_Compras
        LEFT JOIN Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia
        INNER JOIN T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
GROUP BY T_Compras.Guia
      , T_Compras.Data
      , T_Compras.Id_Embarcação
      , T_Compras.[Nº Maré]
      , T_ComprasDet.Id_Peixe
      , T_ComprasDet.Id_Tamanho
      , T_ComprasDet.Id_Frescura
      , Q_Guia_Fact.Valor;
GO

SELECT  Guia
      , Data
      , Id_Embarcação
      , [Nº Maré]
      , Id_Peixe
      , Id_Tamanho
      , Peso
      , Id_Frescura
      , Valor
FROM dbo.View_Example
WHERE Guia = @Guia;
GO

Alternatively, you can use a table-valued function or stored procedure to return the result based on the parameter supplied. The table-valued function is more similar to an Access query definition in that it can be used directly in queries.

CREATE FUNCTION dbo.TVF_Example(@Guia int)
RETURNS TABLE
AS 

RETURN (
    SELECT  T_Compras.Guia
          , T_Compras.Data
          , T_Compras.Id_Embarcação
          , T_Compras.[Nº Maré]
          , T_ComprasDet.Id_Peixe
          , T_ComprasDet.Id_Tamanho
          , SUM([Peso Kg] * [Caixas]) AS Peso
          , T_ComprasDet.Id_Frescura
          , Q_Guia_Fact.Valor
    FROM    T_Compras
            LEFT JOIN Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia
            INNER JOIN T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
    GROUP BY T_Compras.Guia
          , T_Compras.Data
          , T_Compras.Id_Embarcação
          , T_Compras.[Nº Maré]
          , T_ComprasDet.Id_Peixe
          , T_ComprasDet.Id_Tamanho
          , T_ComprasDet.Id_Frescura
          , Q_Guia_Fact.Valor
    HAVING T_Compras.Guia = @Guia
    );
GO

SELECT  Guia
      , Data
      , Id_Embarcação
      , [Nº Maré]
      , Id_Peixe
      , Id_Tamanho
      , Peso
      , Id_Frescura
      , Valor
FROM dbo.TVF_Example(12345);
GO

CREATE PROCEDURE dbo.Proc_Example
    @Guia int
AS

SELECT  T_Compras.Guia
        , T_Compras.Data
        , T_Compras.Id_Embarcação
        , T_Compras.[Nº Maré]
        , T_ComprasDet.Id_Peixe
        , T_ComprasDet.Id_Tamanho
        , SUM([Peso Kg] * [Caixas]) AS Peso
        , T_ComprasDet.Id_Frescura
        , Q_Guia_Fact.Valor
FROM    T_Compras
        LEFT JOIN Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia
        INNER JOIN T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
GROUP BY T_Compras.Guia
        , T_Compras.Data
        , T_Compras.Id_Embarcação
        , T_Compras.[Nº Maré]
        , T_ComprasDet.Id_Peixe
        , T_ComprasDet.Id_Tamanho
        , T_ComprasDet.Id_Frescura
        , Q_Guia_Fact.Valor
HAVING T_Compras.Guia = @Guia;
GO

EXECUTE dbo.Proc_Example  @Guia = 12345;

GO
| improve this answer | | | | |
  • Which one do you think would be better to implement using an Access form? – António Bezerra Aug 6 '15 at 12:36
  • It depends what you mean by "better". A TVP is better aligned with the paradigm of Access query objects but a view or proc provides more abstraction. A proc also provides more options for optimization, including control-of-flow logic (not needed in this case). – Dan Guzman Aug 6 '15 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.