• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

[SQL Server 2012] Create view with variables

Question

tetsu-kun    1
Posted (edited)

Hey guys.

I'm tryin to create a view but I'm using a variable to use in an union with other view, but I can't create the view properly.

My code is the this one:

 

CREATE VIEW QRFACTNCNETA2 AS
DECLARE @IVA INT
SET @IVA=0
SELECT 
DOCUMENTO=
CASE
WHEN D.SERIE='N' THEN 'Nota de Credito'
WHEN D.SERIE='NR' THEN 'Nota de Credito'
END,
CONCAT(d.SERIE,'-',d.NNOT) AS NO_DOCUMENTO,
d.FECHA, 
-d.SUBTOTAL AS SUBTOTAL,
IVA AS IVA,
IVA AS IVAMN,
d.TC, 
d.MONEDA, 
(-D.SUBTOTAL*D.TC) AS SUBTOTALMN, 
d1.NOCLIENTE, 
d1.RAZONSOCIAL,
d2.NOVENDEDOR,
d2.NOMBRE
FROM
    dbo.NOTAS d
    INNER JOIN dbo.CLIENTES d1 ON (d.CLIENTE=d1.NOCLIENTE)
	INNER JOIN dbo.VENDEDOR d2 ON (d1.NOVENDEDOR=d2.NOVENDEDOR)
	WHERE D.NNOT NOT LIKE '0' AND (D.APLICADA=1 OR D.SALDO>0)

I'm using the variable twice time, because in the other view I have 2 type of IVA (normal IVA and IVAMN)

I read that I can use a procedure or a parameter, but I can't get it well, In other example I saw this one, but I can't addapted it to mine:

 

CREATE view v_variables
	AS
		Declare @b DATE
	SET @B=CAST(DATEADD(DD,-7,GETDATE()) AS DATE)
SELECT *
FROM VIEWTABLE
WHERE NAMEDATE>=@b

And they addapted it as this way:

CREATE VIEW v_variables
	AS
	WITH dts AS(
	SELECT CAST(DATEADD(DD,-7,GETDATE()) AS DATE) AS P)
SELECT V.NAME
	  ,V.NAMEDATE
FROM VIETABLE V
CROSS APLY dts d
WHERE V.NAMEDATE>=d.P

 

I tried to apply on my own code, but I can't do it, because I didn't know what put inter the "SELECT CAST" if I need the variable equals 0.

 

Can anybody help me or guide me how I have to do it, please?

Sorry for my poor englis.

Regads.

Edited by tetsu-kun

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0
tetsu-kun    1

I solved, I created the 2 views, when I used the UNION I declareted the variables before and I can use the 2 views with variables.

Regards.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.