Showing posts with label union. Show all posts
Showing posts with label union. Show all posts

Tuesday, March 27, 2012

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Thursday, March 8, 2012

Creating a large dynamic View

I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

EDIT: Changed the title to read Procedurally generating a large view.you have a stored procedure that creates a view with dynamic sql? seems like a bad idea. stored procedures are for DML, not DDL.

why not just store the view definition as a script in source control and execute it as necessary? when your supplier adds columns to their tables, you just add those columns to your script and execute it again.|||I don't really know what you mean by storing it as a script in a source control. By a source control, do you mean a third party utility? I am not familiar with the term. I don't really have any third party utilities or compilers to work with, just SQL Server 2000. The stored procedure that I have has been working fine. I just wanted to find out if there is a more efficient way to do the same thing. Basically, I need the view to look at different tables every day. The tables are indicated at the end of the table name by day and month of the data they contain. The basic outline of the stored procedure I have is below.

Create Procedure ProcName
as
Declare @.DatabaseName as varchar (128)
Declare @.sql as varchar(8000)
Declare @.view_Name as varchar (128)
Declare @.table_Name as varchar (128)
Declare @.ProcDate as datetime
Declare @.cntr as Int
Declare @.sql2 as varchar(8000)
Declare @.sql3 as varchar(8000)


Select @.DatabaseName = DB_NAME()
exec usp_DayToProcess Null, @.ProcDate output

Set @.cntr = 0
Set @.view_Name = 'ViewName'
Set @.Path2 = ''
Set @.Path3 = ''
Set @.Path = 'CREATE VIEW ViewName AS SELECT * FROM ('

While (@.cntr < 15)
Begin
Set @.table_Name = '[TableName_' + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 1, 2) + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 4, 2) + ']'
if exists (select * from dbo.sysobjects where id = object_id(@.table_Name) and OBJECTPROPERTY(id, N'IsUserTable') = 1 and crdate > DateAdd(year, -1, @.ProcDate))
Begin
If @.cntr <> 0
Begin
Set @.Path = @.Path + 'UNION ALL '
End
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
End
Set @.cntr = @.cntr + 1
If Len(@.Path) > 7000
Begin
If Len(@.Path2) > 7000
Begin
Set @.Path3 = @.Path2
End
Set @.Path2 = @.Path
Set @.Path = ''
End
End

Set @.Path = @.Path + ') TempView ORDER BY ...'

EXECUTE (@.Path3 + @.Path2 + @.Path)
GO

Since I have several fields that have to be reformated from the tables as well as functions to perform on some of the fields in order to get the values I need, the sql gets fairly large. So, it ends up taking more than two varchar variables to store all of the sql to search 15 tables. I am trying to standardize the procedure a bit, so in case more than 15 days of tables are required, it would require more variables. I was wondering if there is a more efficient way of doing this with SQL Server 2000 alone.|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control|||What is the point of this:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
Are you manually coding the column names?|||Personally, I like the SELECT * part|||The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control

So, you are basically saying to modify the code each day/week/whenever it needs to be run?

What is the point of this:

Code:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_NameAre you manually coding the column names?

The '...' is where I am specifying the fields to use. I didn't include all of it, because it is a bit long. For instance, I am adding a date field into the view so that the date of the transaction is a field. The tables do not have a transaction date field, since they are a different table for each day. Also, I specify the field names, because there are times that the company who creates the code that makes the tables will change that code during an update. I could check the table for any changes each time they put out updates, but this aggregates several tables. So, some of the tables would be missing fields that others have within the tables that are being aggregated. This would cause an error if the fields to use were not specified.

Personally, I like the SELECT * part

After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

Originally Posted by Ishe
The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels

I know what you mean, but I didn't really design the tables or the code to make the tables. It's just the only thing I have to work with really.|||So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() (http://msdn2.microsoft.com/en-us/library/3y322t50.aspx) in C#. hard to debug, hard to know what the actual state of the system was at any given time.|||After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.|||SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.

I know, and it has an ORDER BY clause at the end, but I have found from experience with prior UNION views that if you slap an ORDER BY clause at the end of the last union, it doesn't order the entire result set, just the last SELECT. So, I wrapped the entire UNION query making the UNION query a subquery and put the ORDER BY clause at the end of the wrapping query.

Originally Posted by Ishe
So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.

Does that include creating tables through stored procedure?

In this case I am trying to create this in such a way that it won't take someone that knows anything much about SQL Server to be able to use the procedure. Since the tables that the user would need to use change on a daily basis, I don't know of any other way to accomplish this. I can't rewrite the code for them every day. I am actually trying to change the code to be less customized, not more so.|||The '...' is where I am specifying the fields to use.If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.|||If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.

The only reason that it is done in a stored procedure is because the tables that I have to work with are daily transaction tables. The table names indicate the day and month of the transactions that are contained within. I don't think I can create a standard view with daily changing table names.|||But you don't have standard columns!|||But you don't have standard columns!

I'm not sure what you mean. The below contains the code that I replaced '...' with in the example.

Set @.Path = @.Path + 'SELECT FB_MBRNO AS [Member No], Convert(datetime, ' + CHAR(39) + Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 102) + CHAR(39) + ') AS [Tran Date], FB_TLR AS Tlr, FR_TRAN_CODE AS [Tran Code], FB_STATUS AS [Status], FB_CNV_CASH AS CnvCash, FR_TIM AS [Time], FB_CASH AS [Cash], FB_CHECK AS [Checks], FB_APPLIED AS Applied, FB_CASHBACK AS [Cash Back], FB_REVERSED AS Reversed, FB_MISC_CODE As [Misc Code], FB_TRAN_NO As [Tran No] FROM ' + @.table_Name

I think the columns are fairly standard, except the [Tran Date] field, which is there because the tables I am looking at do not have a date field that indicates when the transaction occured.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.

I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.|||I guess you have no choice then. I wouldn't use that software provider again if I were you. ;)|||I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.

Have I mentioned lately I hate 3rd party vendors?

There is no silver bullet|||Have I mentioned lately I hate 3rd party vendors?Not often enough.

Tuesday, February 14, 2012

CREATE VIEW w/ UNION

I have the following query that works fine when run manually, but I don't
know how to create a view of the equivalent data. When I uncomment the
CREATE VIEW it errors out.
--CREATE VIEW vwCPA
--AS
DECLARE @.TypeA nchar(10)
DECLARE @.TypeP nchar(10)
DECLARE @.TypeC nchar(10)
SET @.TypeA='Adjustment'
SET @.TypeP='Payment'
SET @.TypeC='Charge'
SELECT @.TypeA AS Type, *
FROM vwAdjustmentDetail2
UNION
SELECT @.TypeP AS Type, *
FROM vwPaymentDetail2
UNION
SELECT @.TypeC AS Type, *
FROM vwCharges2
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.sessioncomputing.comPatrick Rouse wrote:

> I have the following query that works fine when run manually, but I
> don't know how to create a view of the equivalent data. When I
> uncomment the CREATE VIEW it errors out.
> --CREATE VIEW vwCPA
> --AS
> DECLARE @.TypeA nchar(10)
> DECLARE @.TypeP nchar(10)
> DECLARE @.TypeC nchar(10)
> SET @.TypeA='Adjustment'
> SET @.TypeP='Payment'
> SET @.TypeC='Charge'
You can't use variables in views. Create a stored procedure instead.
HTH,
Stijn Verrept.|||"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5456E06E-4869-46CD-A177-C9A1B2580CFE@.microsoft.com...
>I have the following query that works fine when run manually, but I don't
> know how to create a view of the equivalent data. When I uncomment the
> CREATE VIEW it errors out.
> --CREATE VIEW vwCPA
> --AS
> DECLARE @.TypeA nchar(10)
> DECLARE @.TypeP nchar(10)
> DECLARE @.TypeC nchar(10)
> SET @.TypeA='Adjustment'
> SET @.TypeP='Payment'
> SET @.TypeC='Charge'
> SELECT @.TypeA AS Type, *
> FROM vwAdjustmentDetail2
> UNION
> SELECT @.TypeP AS Type, *
> FROM vwPaymentDetail2
> UNION
> SELECT @.TypeC AS Type, *
> FROM vwCharges2
>
Include the literal values in the queries, perhaps with an explicit cast.
And assuming that none of the individual queries returns duplicate rows, use
UNION ALL since your Type column guarantees that no row will be duplicated
between queries.
EG
CREATE VIEW vwCPA
AS
SELECT cast(N'Adjustment' as nchar(10)) Type, *
FROM vwAdjustmentDetail2
UNION ALL
SELECT cast(N'Payment' as nchar(10)) Type, *
FROM vwPaymentDetail2
UNION ALL
SELECT cast(N'Charge' as nchar(10)) Type, *
FROM vwCharges2
David|||Thanks David, that's exactly what I was looking for.
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.sessioncomputing.com
"David Browne" wrote:

> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5456E06E-4869-46CD-A177-C9A1B2580CFE@.microsoft.com...
> Include the literal values in the queries, perhaps with an explicit cast.
> And assuming that none of the individual queries returns duplicate rows, u
se
> UNION ALL since your Type column guarantees that no row will be duplicated
> between queries.
> EG
> CREATE VIEW vwCPA
> AS
> SELECT cast(N'Adjustment' as nchar(10)) Type, *
> FROM vwAdjustmentDetail2
> UNION ALL
> SELECT cast(N'Payment' as nchar(10)) Type, *
> FROM vwPaymentDetail2
> UNION ALL
> SELECT cast(N'Charge' as nchar(10)) Type, *
> FROM vwCharges2
>
> David
>
>

Create view using UNION

I have 2 tables with exact structure:
Employee:
ID
FirstName
LastName
Position
ExEmployee:
ID
FirstName
LastName
Position
And the reference table
Position:
ID
Name
I want to create a view something like:
CREATE VIEW viewEMPLOEE_ALL
as
SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS LastName,
p.Name AS Position
FROM Employee e
LEFT JOIN Position p ON p.ID = e.Position
ORDER BY e.LastName, e.FirstName
UNION ALL
SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
LastName, p.Name AS Position
FROM Employee ex
LEFT JOIN Position p ON p.ID = ex.Position
ORDER BY ex.LastName, ex.FirstName
This is similar to my actual SQL as my original SQL is much much more table
join.
I got an error when I tried to run this SQL in Query Analyser:
"Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query."Man Utd wrote:
> I have 2 tables with exact structure:
> Employee:
> ID
> FirstName
> LastName
> Position
>
> ExEmployee:
> ID
> FirstName
> LastName
> Position
>
> And the reference table
> Position:
> ID
> Name
> I want to create a view something like:
> CREATE VIEW viewEMPLOEE_ALL
> as
> SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS
> LastName, p.Name AS Position
> FROM Employee e
> LEFT JOIN Position p ON p.ID = e.Position
> ORDER BY e.LastName, e.FirstName
> UNION ALL
> SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
> LastName, p.Name AS Position
> FROM Employee ex
> LEFT JOIN Position p ON p.ID = ex.Position
> ORDER BY ex.LastName, ex.FirstName
> This is similar to my actual SQL as my original SQL is much much more
> table join.
> I got an error when I tried to run this SQL in Query Analyser:
> "Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query."
Remove the ORDER BY in the first SELECT and use the ordinal position for
the ORDER BY in the second only if absolutely necessary. You're better
off not using an ORDER BY at all since it just creates overhead. You can
always add the ORDER BY later on to any queries you write against the
view.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I removed the ORDER BY clause in each SELECT but still got the same error.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Man Utd wrote:
> Remove the ORDER BY in the first SELECT and use the ordinal position for
> the ORDER BY in the second only if absolutely necessary. You're better
> off not using an ORDER BY at all since it just creates overhead. You can
> always add the ORDER BY later on to any queries you write against the
> view.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hi,
Sorry, my careless mistake.
In my second SELECT statement I have a CASE comparing the "ExEmployee" table
field, but I used "Employee" instead.
Solved.
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:Om3fuTr2FHA.3912@.TK2MSFTNGP15.phx.gbl...
> I removed the ORDER BY clause in each SELECT but still got the same error.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
>

create view

CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;

its working with oracle

how to chenge in t-sql

thanx

Hi,

you have to ALIAS Your SubQuery.

CREATE VIEW getUsedColumns
AS
SELECT * FROM
(
SELECT DISTINCT attr
FROM iba
UNION
SELECT DISTINCT attr
FROM new_iba
) SomeSubQuery
WHERE attr != 0;

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

You need to specify an alias for the derived table. And you don't really need the DISTINCT in each of the SELECT statements since the UNION will eliminate the duplicates anyway. You will get better performance by writing it as:

SELECT * FROM (SELECT Dattr FROM iba UNION SELECT attr FROM new_iba) as t

WHERE attr != 0;