CODEWITHSUNDEEP
sqlvariables
Puneeth
Puneeth

Reputation: 33

How to declare and use variables in SQL

This is my query - please help me with how to use variable in the below query [Variable marked with Bold]

create view cost_sheetNO (cost_No, line_No, EC, ATE, SP, E_GMValue, E_GMperc)
as  
    select 
        Vcost_sheet_line.[Cost Sheet No_], 
        Vsales_line.[Costsheet No_], 
        Vcost_sheet_line.[Landing Price],
        (select SUM(Vcost_sheet_line.[Landing Price]) 
         from Vcost_sheet_line 
         where Vsales_line.[Costsheet No_] = Vcost_sheet_line.[Cost Sheet No_]),
        Vcost_sheet_line.[List Price] * Vcost_sheet_line.[Special Vendor Discount _],
        (select declare @a decimal = sales_headerSO.OV - sales_headerSO.EC 
         from sales_headerSO), 
        (select @a - sales_headerSO.EC 
         from sales_headerSO)
    from 
        Vcost_sheet_line, Vsales_line 
    where 
        Vsales_line.[Costsheet No_] = Vcost_sheet_line.[Cost Sheet No_]

Thanks

Upvotes: 0

Views: 240

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6455

A view doesn't have parameters, but you could implement a tabular function (your code looks like SQL Server) to get the same result depending on a parameter.

create function cost_sheetNO (@a decimal) 
returns table
return (
  select Vcost_sheet_line.[Cost Sheet No_], Vsales_line.[Costsheet No_], Vcost_sheet_line.[Landing Price], 
         (select SUM(Vcost_sheet_line.[Landing Price]) 
          from Vcost_sheet_line 
          where Vsales_line.[Costsheet No_] = Vcost_sheet_line.[Cost Sheet No_]), 
         Vcost_sheet_line.[List Price]*Vcost_sheet_line.[Special Vendor Discount _], 
         (select @a - sales_headerSO.EC from sales_headerSO)       
          from Vcost_sheet_line, Vsales_line       
          where Vsales_line.[Costsheet No_] = Vcost_sheet_line.[Cost Sheet No_] 
)

Upvotes: 1

memo
memo

Reputation: 1938

SELECT column INTO @variable

if I understand your problem correctly

Upvotes: 0

Related Questions