View That Unions Two Other Views Does Not Work

May 1, 2006

I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.

When I run the two views as below it works fine

Select * from V1
Union
Select * from V2


I then created another view V3 of the above union as

Create view V3
As
Select * from V1
Union
Select * from V2

Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables

View 1 Replies


ADVERTISEMENT

Sql Views - Embedded View Work-a-rounds

Jul 11, 2007

hi guys,

i've been asked to re-write a sql view. the view itself contains several calls to other views (embedded). is there a way to get around using embedded views. I've written the same query up using temp. tables but obviously temp. tables can't be used in views?

Is there any special things I should be looking for?

View 2 Replies View Related

SQL Views - Embedded View Work-a-rounds

Jul 12, 2007

Hi guys,I've been asked to re-write a sql view. The view itself containsseveral calls to other views (embedded). Is there a way to get aroundusing embedded views. I've written the same query up using temp.tables but obviously temp. tables can't be used in views?Is there any special things I should be looking for?

View 2 Replies View Related

Multiple Unions Where There Are No Results In First Set Causes All Following Unions To Return Nothing.

Mar 21, 2008



I have two tables one with current data and a second with the same design that holds purged history.

I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.

Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID

But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.

Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory


And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?

View 4 Replies View Related

How Can You Optimize Duplicated Work In Views?

Jul 20, 2005

This one's kind of hard to explain, so I've opted to post a simplifiedversion of our view that prompted me to ask this question: Thequestion is re-asked after the view...create view MainView (PrimaryKeyID,SubTotal1,SubTotal2,GrandTotal)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total),sum(t1.Total) + sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...Notice in the 3rd column called "GrandTotal" how it calls the function"sum" two more times. Common sense tells me that this is notnecessary. in our case it's orders of magnitude worse... Is the queryoptimizer smart enough to only call these sums once per row in"SomeTable"? Common sense tells me that if we were to break the viewsapart into two views it would avoid this ineffeciency:create view InnerView (PrimaryKeyID,SubTotal1,SubTotal2)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...create view OuterView (PrimaryKeyID,SubTotal1,SutTotal2,GrandTotal)asselect iv.PrimaryKeyID,iv.Total1,iv.Total2,iv.Total1 + iv.Total2from InnerViewNotice how it appears that we've tricked the optimizer into thinkingthere are less operations. So my question is how does views handlethis situation? Does the optimizer treat both version the same? Or isone faster than the other? Or is there another, faster way? Doesadding levels of views slow down things, or are views simply likemacros and get removed when compiled (I think I've read the latter istrue actually)Thanks,Dave

View 1 Replies View Related

Packages And Data Source Views Do Not Work?

Jun 16, 2006

Hi,

doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.

1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries

When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.

The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.

How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?

Cheers,
Frank

View 5 Replies View Related

Permissions To View INFORMATION_SCHEAM Views

Aug 9, 2002

SQL7, sp3

What specific permissions do you need to be able to view information_schema views? I thought public role had permissions to select on these views, but this is not the case? What do I do?

my developers have db_reader, db_writer, and db_ddladmin. They do not have db_owner. If I make them Sysadmin in sql they can view them, but that doens't fit in our security setup we have. THoughts?

Thanks,

View 2 Replies View Related

View That Would Output Data From Two Views

Aug 8, 2007

OK,

I have two data tables where depending on the time line item it is, it needs a different join.

So, it's like this:

Table Credit Card
Table Memo

If Credit Card is VISA, join on column A
If Credit Card is MA, join on column B

So, I created two views. One view has all the info joined on column A. The other view has all the info joined on column B.

How do I create a third view that will output all the data from View A and View B without making it into a cartesian product?

I can't change the table structure due to legacy application/data issues.

Thanks!

View 3 Replies View Related

Does A View Refresh Itself When I Reference It? (was Question On Views)

Feb 24, 2006

guys, ive never worked with Views before so forgive me.
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.

if i create a view:

CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable


When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'

does that View Refresh itself??

in other words does it run each time i Reference it??? or is it static from when i created it.

Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?

thanks for any help
rik

View 3 Replies View Related

CREATE VIEW, Seperate One Column In Two Views..

Oct 17, 2007

I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----


From the table meta_host_types;

id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid

I would like to seperate value into type_value and connection_value, because it holds both values.

Any tip?

bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)



d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)

View 3 Replies View Related

Transact SQL :: Combine Two Views Into One Statement Or One View

Oct 15, 2015

I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.

CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,

[Code] ......

View 3 Replies View Related

Creating A Single View From 2 Existing Views

Aug 9, 2007

I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON

The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:

EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)


Below is my select command:

------

SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT

GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

ORDER BY
dbo.vw_SEASON06.EVENT

-----

This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.

How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?

Any help is appreciated!

View 7 Replies View Related

Im New To Using Views, And This Program They Have Me Working On Is Using Them Quite Heavily..(cant Read From A View)

Oct 11, 2007

This is my first post, so if i have not posted things in the best manner please lemme know how to be more informative,clear, so that i can learn.

So i have made a view with the following command



GO

/****** Object: View [dbo].[AppraisalView_C] Script Date: 10/11/2007 12:10:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[AppraisalView_C]

AS

SELECT a.Counter

,a.DateCreated

,a.DateModified

,a.UserCreated

,a.UserModified

,a.AppraisalDate_C

,a.TypeID_C

,a.Customer_C

,a.Employee_C

,b.Notes_C

,b.Value_C

,b.AppraisalLineItemID_C

,b.AppraisalID_C

FROM dbo.Appraisal_C AS a

INNER JOIN dbo.AppraisalLineItem_C AS b ON a.AppraisalID_C = b.AppraisalID_C



---------------------------------------------------------------------------------------

the program im working on creates the SQL call to read from this view and creates
the following query


SELECT A.[AppraisalDate_C], A.[AppraisalID_C], A.[AppraisalLineItemID_C], A.[Customer_C], A.[Employee_C], A.[Notes_C], A.[TypeID_C], A.[Value_C]

FROM AppraisalView_C A

WHERE [AppraisalView_C].[AppraisalID_C] = 'APP-000006'


but I end up getting the dreaded "Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AppraisalView_C.AppraisalID_C" could not be bound." error....

I cant change the Query that is called, but i can change the view, what is wrong?

View 4 Replies View Related

BCP Utility Doesn't Work With View

Mar 16, 2015

I am using BCP utility to export data from a view to text file. I get the following error

"Copy direction must be either r 'in', 'out' ..." when I execute the following

exec master..xp_cmdshell 'bcp [Salesforce_Prod].[dbo].[Project With Opps] out c: est99.txt -c -t, -T -S'

The same thing work if I replace the view in the statement above with a table name.

View 3 Replies View Related

How Does A View Over A Link Server Work?

Sep 21, 2007

Hallo

I got a Link Server (IBMDASQL) and a view. And I am wondering how a view works.

If I try:
select * from [myView] where [myView].A = @A

Dos the Link Server retrieve the complete table and the SQL server applies the where condition?

Or

Does the Link Server use the where condition and retrieve just the requested records?

View 6 Replies View Related

View Of All User Objects (Tables, Views) With Their Replication State NEEDED...

Jun 22, 2007

Hi!



There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):






Code Snippet

CREATE VIEW [dbo].[ViewREPL_PublishedObjects]

AS

SELECT TOP 100 PERCENT

CASE [xtype]

WHEN 'U' THEN 'Table'

WHEN 'V' THEN 'View'

ELSE NULL END AS [Object Type],

[name] AS [Object Name],

CASE WHEN [replinfo] = 0

THEN 0 ELSE 1

END AS [Replicated]

FROM [sysobjects]

WHERE

[xtype] in ('U', 'V')

AND [status] > 0

ORDER BY

(CASE [xtype]

WHEN 'U' THEN 1

WHEN 'V' THEN 2

ELSE 10

END),

[name]



Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].



So, I need a view with same functionality, that will work under SQL-2005 and 2008.



Please, help!

View 2 Replies View Related

Cannot Get My TREE VIEW Recursive Query To Work

Mar 17, 2007

My Table Structure

Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....

MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy

My OUTPUT============

All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.

Can you help me please?

View 12 Replies View Related

View Ignoring Order By - Used To Work In Sql 2000

Dec 13, 2005

This works fine in  SQL 2000, but not in SQL 2005

View 12 Replies View Related

SQL Server Admin 2014 :: How To Grant User Permission To View Specific Views

Aug 5, 2015

I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....

View 3 Replies View Related

My Data-Driven Subscriptions(DDS) Don't Want To Work Although I Can View My Reports

Nov 28, 2006



Hi

I have been asked to move our entire system over from SQL2000 to SQL2005, i eventually got our database and Cubes over to 2005 and converted all my reports to 2005 and deployed them successfully on RS2005

All the subscriptions have been made and they seem to work when i click to view them. Although when i create a DDS for all my reports to run then they fail. Now i've got over 5000 reports to build and i'm definately not going to create each report manually. My SQL Reports huild successfully, it just seems to be the MDX Reports with a problem, i;ve checked my stored procs and they seem fine, i've even seen the [Clients].&[All.Clients] difference from SQL2005 ---->&<------ that is not in SQL2000.

If someone has, or has had the same problem before would you please help me. Any advice or suggestions would be more than welcome.



Kind Regards

Eagle

View 1 Replies View Related

SQL Server 2012 :: Index View Won't Work With Hierarchical Tables?

Apr 4, 2015

Consider following code:

SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID

This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).

Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:

Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".

I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.

How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.

View 3 Replies View Related

In Query Builder Design View, How Does The Filter Operator MDX Work?

Nov 12, 2007

Good morning all,

I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.

In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.

Does anyone have any links to documentation on how to use this operator?

Thanks,
Kathryn

View 2 Replies View Related

How Many Unions?

Jul 18, 2000

I know the answer must be somewhere, but for the moment every place I look ...

How many tables can be unioned together (I think I remember that 6.5 had a limit of 16) in SQL7?

Thanks,
Judith

View 2 Replies View Related

Groupby Unions

May 6, 2004

hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono

View 4 Replies View Related

Creating Unions In TSQL

Aug 11, 2004

Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:


CREATE Procedure spGetReturnCheckForCriteria

@SearchCriteria VARCHAR(8000),

@SortOrder VARCHAR(8000),

@PageSize INT

AS

-- Declare vars

DECLARE @SQLStatement NVARCHAR(4000)

DECLARE @bldSQLStatement VARCHAR(8000)

DECLARE @retValue INT

-- Initialize vars

SET @SQLStatement = ''

SET @bldSQLStatement = ''

SET @retValue = -1

-- Sanity Check(s)

IF (@PageSize IS NULL OR @PageSize < 1)

-- Paging Size can not be Null, nor less than One

BEGIN

SET @RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)

RETURN

END

-- Build the Paging SQL Statement

SET @bldSQLStatement = 'SELECT TOP '

-- Add the Page Size

SET @bldSQLStatement = @bldSQLStatement + CAST(@PageSize AS VARCHAR)

-- Add Columns/Tables/Relationships

SET @bldSQLStatement = @bldSQLStatement + '

ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,

CASE

WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate

ELSE ReturnCheck.UpdatedDate

END AS ReturnCheckTimeStamp

FROM TaxPayment

INNER JOIN DistributionPoint

ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)

INNER JOIN ReturnCheck

ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)

'

-- Add Search Criteria

If (@SearchCriteria IS NOT NULL)

SET @bldSQLStatement = @bldSQLStatement + ' WHERE ' + @SearchCriteria

-- Add Sort Order

IF (@SortOrder IS NOT NULL)

SET @bldSQLStatement = @bldSQLStatement + ' ' + @SortOrder

-- Set the SQLStatement

SET @SQLStatement = @bldSQLStatement
-- Execute the Paging Query
EXECUTE @retValue = sp_executeSQL @SQLStatement
GO


Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.

View 3 Replies View Related

Syntax Error With Unions?

Oct 2, 2001

I doing a union of two select queries, and I keep getting
the following error:

syntax error converting the nvarchar value 'foo' to a column of data type int.

I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.

The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.

Is this a common problem? I'd love to know what I'm doing wrong.

View 1 Replies View Related

Syntax Error With Unions?

Oct 2, 2001

I doing a union of two select queries, and I keep getting
the following error:

syntax error converting the nvarchar value 'foo' to a column of data type int.

I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.

The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.

Is this a common problem? I'd love to know what I'm doing wrong.

View 1 Replies View Related

Ordering In Conjunction With Unions

Oct 7, 2004

Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...


SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName

UNION ALL

SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName


Thanks for any advice

Aaron

View 4 Replies View Related

Performance Issue With Unions In Sp's?

Jul 20, 2007

I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.

Is there such an issue I've missed?

Mike

View 6 Replies View Related

Doing Unions With Uneven Column Amounts

Mar 14, 2006

I'm creating a program that allows users to submit a report on equipment at regular intervals. If a piece of equipment has a problem, it is given a job entry that refers back to the report for various information.

However, there will be times when a problem is noticed, and someone wants to submit it immediately; these are made into extra jobs.

To this end, I have three tables:
Reports
Jobs
ExtraJobs

Because ExtraJobs cannot be associated with a report, they have their own table, which holds information that would otherwise be grabbed from both Reports and Jobs. While there are seperate submission forms for regular jobs and extra jobs, I would like them to appear on the same query result when a user looks at submitted jobs or reports.

What I'm currently trying to do is this:
Code:

SELECT*
FROMReports LEFT JOIN Jobs
ON Reports.reportID = Jobs.reportID
UNION ALL
SELECT ExtraJobs.*
FROM ExtraJobs


This won't work because the first half of the union has an extra column (reportID) that the second half does not. Is there any way to add in a value for that non-existant column (say, ExtraJobs.reportID = -1) to make sure that both sides have an equal number of columns?

If worst comes to worst, I could add a reportID column to ExtraJobs and have it set to -1 for everything, but I'd like to keep from adding fat, if at all possible.

View 2 Replies View Related

Unions Two Tables Into A Temp Table

Aug 31, 2007

How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?

View 4 Replies View Related

Way To Group / Count Multiple Unions Together?

Dec 9, 2013

I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:

(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35

[code]....

View 4 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved