Problem With Query On A View Created By A Union

Jun 27, 2006

Hi,

I have a view V1 created as a plain UNION from 2 tables:

SELECT * FROM T1
UNION
SELECT * FROM T2

If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned.
However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.

Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?

thx,
smiley61

View 2 Replies

Created View With Join Query - Unable To Fetch Details

Feb 12, 2014

I created a view with a simple join query

there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as

"select orderid from joins where orderid=NULL"

Query :

create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid

View 1 Replies View Related

Indexes On Datasets Created Froma Union All Task

Apr 25, 2007

Hi

I have a package that takes data from 3 different sources, unions them all together and then performs a lookup against a tabel on a different server. The 3 sources of data creates a dataset with quite a large volume of rows (approx 100,000) and the look up takes an age to complete.

Is there anyway of adding an index of sorts to the dataset created by the union to speed the process up? I guess i could output the union all to a temp table and look up against this but wondered if there was another way

thanks

scott

View 8 Replies View Related

Creating A View Using A Union?

Jan 14, 2015

I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.

This is my code so far:

Create View Table_AB As
Select *From Table_A
Union All
Select*From Table_B

View 1 Replies View Related

Error When Creating View With Union

Jul 10, 2014

I’m receiving the following message when attempting to run the SQL statement below.

Error report:
SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2"
Failed: Warning: execution completed with warning

-----------------------
CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS
SELECT DISTINCT code, name
FROM
(
SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6
FROM UIP_SEGMENT_VALUES
WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v)
UNION
SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL)
);
----------------

Referenced View Columns:

"SEG_VIEW_PARENTS" ("SEG_NAME", "CODE", "NAME")

Referenced Table Columns:"UIP_SEGMENT_VALUES"
"SEG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_DESC" VARCHAR2(200 BYTE),
"SEG_TYPE" VARCHAR2(20 BYTE),
"SEG_COMPANY" VARCHAR2(20 BYTE)

View 1 Replies View Related

Using Union And Join In A Single View

Aug 8, 2006

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.

View 7 Replies View Related

How To Bring Extra Columns In A UNION ALL View?

Jul 19, 2006

I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T

UNION ALL

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T

This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.

The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.

My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?

View 2 Replies View Related

Alternative To Creating View With Union In Two Databases?

Jul 20, 2005

I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt

View 2 Replies View Related

How To Create View Of Union On Variant Tables ?

Apr 1, 2008

HI, Guys:

I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'

Now I try to create a view AT which is the union of all these AT_XX tables, such as:

Code Snippet

Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...

but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure

I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.

So is there any way to achived my goal?
any reference would be appreciated, thanks !

View 8 Replies View Related

How To View/edit SSIS Packages Already Created?

May 7, 2007

I created a few in SqlServer2005 DEV version and ran them manually with success. But where are they? I chose the SqlServer storage option and I see them when I create Job and steps but how do I view/edit these packages from SqlServer Mgmt. Studio? The SSIS service is running.

View 6 Replies View Related

SQL Server 2014 :: UNION ALL In View With Temp Table

May 8, 2015

I have a performance issue with one of the views when I join the view with a temp table

I have 2 Views - View1 and View2.

There is a third view - view_UNION where the

view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2

If I have a query like -

Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID

the execution is too slow.

But if I execute the views separately, I get good performance.

How to improve the performance of the view_Union

View 7 Replies View Related

Avoid Table Scan Using Multi Union View

May 26, 2008

Anyone see a way to trick the optimizer into not scanning all tables involved in the view?

-- create two test tables
create table dbo.test1
(testID int, TestName varchar(10))

create table dbo.test2
(testID int, TestName varchar(10))

-- populate
declare @i int
set @i = 1000
while @i > 0
begin
insert into dbo.test1
select @i, '1.' + cast(@i as varchar(5))
set @i = @i - 1
end

insert into dbo.test2
select 1, '2.1' union all
select 2, '2.2'
go

-- create view
create view dbo.vw_Test
as
select1 as QueryID,
TestName
fromdbo.Test1
union all
select2 as QueryID,
TestName
fromdbo.Test2;
go

-- this works as i want, only scans table dbo.Test2
select *
fromdbo.vw_Test
whereQueryId = 2

-- joining to a table triggers scan of both tables in view:
declare @table table (QueryID int)
insert into @table
select 2;

selectvt.TestName
fromdbo.vw_Test vt
join@table t on
vt.QueryID = t.QueryID

Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.

Nathan Skerl

View 6 Replies View Related

Deadlock In View With Select Union - Creating A/S Dimension

Jul 23, 2005

I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null

View 1 Replies View Related

Created View In Sql Management Studio Not Showing In Ssrs

Mar 17, 2008

hi all

Using Sql Server 2005 / Reporting Services

I created a view in sql server management Studio but can't see it in Reporting Services.

I appear to have the same security permissions?

what else am I missing?

thanks
jewel

View 1 Replies View Related

Analysis :: How To Save Parameter From A Report Created In Browse View Under Cube

Nov 10, 2015

After connected to Analysis Services and following the path below:

Analysis Service - Databases - Cubes

I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.

I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?

View 3 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

How To Wrap A UNION Query In A Totals Query?

Jul 20, 2005

I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell

View 9 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Union Query Help

Mar 27, 2006

I need help with a union query. My table structure is as follows:
OffierID (key field)
CaseFileID
CurrentOffer
PrevOffer

There table can have multiple entries per CaseFileID.
I need a query that will tell the highest value in Current Offer or
PrevOffer for each CaseFileID. I have a union query that combines
CurrentOffer and PrevOffer and then selects the top value for a
specific CaseFileID; however, I want to have a complete list of
CaseFileIDs with one value for PrevOffer. My current query is as
follows:

SELECT TOP 1 Offer FROM
(
select CurrentOffer as PrevOffer
FROM tblOffers
WHERE tblOffers.CaseFileID = @CaseFileID AND CurrRank <> 1
UNION
select PrevOffer as PrevOffer
FROM tblOffers
WHERE tblOffers.CaseFileID = @CaseFileID
) tmp
ORDER BY 1 desc

How can I get this to work for all CaseFiles? Thanks for your help.

View 4 Replies View Related

Union Query

Jul 10, 2004

I want to perform the task which is querying the table using the select statemtent and then inserting some values using the insert/update statement.

Can i perform the both (select and insert) using the union statement.

View 1 Replies View Related

UNION Query

Aug 9, 2004

Hi gusy, this is the first time I am trying to use "Union" query. I am trying to create a view(linking and taking data from 3 tables) so I can create a crosstab report out of it.

Basically one table contains about 12 fields and I am trying to grab data from all of them is they are not null.So this is my query,but when it executes it only dispalys result from the first query,what am I doing wrong.

SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarInsurance
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarInsurance IS NOT NULL)
UNION
SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarTransport
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarTransport IS NOT NULL)

Thanks

View 1 Replies View Related

A WHERE In A Union Query

Apr 22, 2004

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable

Does anyone know how to write this correctly?

View 8 Replies View Related

Union With Sub Query

Jul 23, 2005

I've been trying to do a union with a subquery - I've made a differentexample which follows the same principles as follows:First bit brings back accounts which are in the top 10 to 15 by accountname.Second bit brings back accounts which are in the bottom 10 to 15 byaccount name.I want to union the two result sets together. These selects work asthey are, but don't when i take the comment away from the unionoperator.select top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName asc)order by c1.accountName asc--union allselect top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName desc)order by c1.accountName descSo my problem is really about how to have an order by in a sub querywhich is then used in a main query which is then unioned with anotherquery - SQL Server doesn't seem to like that combination of things. Anyclues anyone?Cheers,NAJH

View 5 Replies View Related

Union Query

Jul 23, 2005

hi,Can you union 2 queries with an IF statement between them?e.g.select a, bfrom mtTablewhere a = cunionif ab = xbeginselect a, bfrom mtTablewhere a = cendCheers,Jack

View 4 Replies View Related

Query: Union On Self

Jul 10, 2006

Hello every body.I have a small issue.Problem: I have a table with 4 descriptor columns (type). I need toformulate a query to retrieve a count for each type so I can groupby...etc. The view I have works, but doesn't work when I supplement thequery with some functions... they just don't like the UNION. The realproblem is I can't change any of the udf's or queries, just the view.The view is inner joined back on to the primary table 'qt_ins' againand a heap of other tables. But for this post and to not complicate ittoo much I've just included the primary table and the view...Also my querys work if I don't put a where clause on for the VIEW. eg:.... and cv.type = 'Environmental'.... for some reason with a clause itgets stuck in an *infinite loop.Conditions: The table structure cannot be changed in anyway. Theview/query must return 2 columns qi_id & type.I considered creating a function to return the Types but then I figuredI would ask you folks for a better way.Any help with the view appreciated.Thank you.The below will create the table, with sample data and the view.---------------------------StartQuery--------------------------------------------CREATE TABLE [dbo].[qt_ins] ([qi_id] [int] NOT NULL ,[qi_injury] [bit] NULL ,[qi_environmental] [bit] NULL ,[qi_equipment_damage] [bit] NULL ,[qi_vehicle] [bit] NULL) ON [PRIMARY]GOINSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (20,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (21,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (23,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (24,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (25,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (26,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (27,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (28,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (29,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (30,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (31,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (32,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (33,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (34,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (35,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (36,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (37,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (38,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (39,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (40,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (41,0,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (42,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (43,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (44,0,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (45,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (46,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (47,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (48,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (49,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (50,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (51,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (52,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (53,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (54,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (55,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (56,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (57,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (58,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (59,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (60,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (61,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (62,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (63,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (64,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (65,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (66,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (67,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (68,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (69,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (70,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (71,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (72,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (73,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (81,1,0,0,0)GOCREATE VIEW dbo.v_qt_in_typeASSELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1UNION allSELECT qi_id, 'Environmental' AS type FROM qt_ins WHEREqi_environmental = 1UNION allSELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHEREqi_equipment_damage = 1UNION allSELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1GOselect count(*),type from v_qt_in_type group by type---------------------------ENDQUERY--------------------------------------

View 7 Replies View Related

UNION ALL Query - HELP!

Jul 20, 2005

I am having conceptual trouble with the following query:select r.ServiceID,r.ContractID,sum(Total)from (selectcsc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Totalfromiwms_tbl_CustomerSiteContainers csc,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andcsc.ServiceID > 1 andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = csc.ServiceIDgroup byc.ContractID,csc.ServiceIDunion allselectsi.ServiceID,c.ContractID,sum(j.QuantityCollected) -sum(j.QuantityDelivered)as Totalfromiwms_tbl_Jobs j,iwms_tbl_ServiceInstances si,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = si.ServiceID andj.JobStatusCode <> 80 andj.ServiceInstanceID = si.ServiceInstanceID andsi.ServiceID > 1group byc.ContractID,si.ServiceID) as rgroup byr.ContractID,r.ServiceIDhavingsum(Total) <> 0order by r.ContractIDIt returns 140 rows. However, if I comment out the first selectstatement inside the brackets (select csc.ServiceID,c.ContractID....union all) and run it, it returns 4,785 rows. If I comment out thesecond select statement (union all ...group byc.ContractID,si.ServiceID) it returns 4,786 rows. So why doesn't the*whole* thing return 9,571 rows? That's what I thought a UNION did -append the results of one select to the bottom of the second select.I will supply table defs if it will help, but there's a lot of stuffhere and I think it isn't a data question, but anI-don't-understand-the-SQL question!TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk

View 3 Replies View Related

SQL Query Help. UNION ALL

Jan 30, 2008

Hi, I have a question,

Im trying to use the UNION ALL statement to join my results.
I have multiple talbes, each table has the table name as a date: 28_1_2008, 29_1_2008, etc..
Some dates dont have tables.

What I want to do is write an sql query that gets all the results from a rage of dates..

ie. range : 27_1_2008 to 29_1_2008

What im doing :

sql1: Select * from 27_1_2008 UNION ALL select * from 28_1_2008 UNION ALL select * from 29_1_2008.

It works fine if all tables exist, but in my data base every date DOESNT NECESSARLY have a table.

ie. say I have tables 27_1_2008 , 28_1_2008, 30_1_2008

Now if I do

sql2: Select * from 27_1_2008 UNION ALL Select * from 28_1_2008
This works.

But,
sql3: Select * from 28_1_2008 UNION ALL Select * from 29_1_2008 UNION ALL Select * from 30_1_2008

Causes an error, and DOESNT return any records..

I need a way so that even if one particular table doesnt exist, the others should be returned.

i.e in sql3, it should skip the non existing "29_1_2008" table, and return the rest. As of now, it doesnt return anything,

Anyone with some suggestions?

View 10 Replies View Related

Union Query?

Feb 8, 2008

I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

which produces the following:

LI_759
-0.001
13:28:20

The second query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value) DESC, CONVERT(varchar(15), DateTime, 108) DESC

Produces the following:

LI_759
0.3661
06:09:30

I have tried the previous answers to this post and none of them worked, i get various errors saying sql is unable to parse. what I would like to have is one query that produces the following:

min:

LI_759
-0.001
13:28:20

max:

LI_759
0.3661
06:09:30

Thank You.

View 3 Replies View Related

UNION Query..need Help!!!!!

Apr 17, 2008

currently I am investing some time in learning SQL, I have been working with a sample db, and have come across UNION.
I see many examples showing the same thing, 2 tables with identical datatype and either with a alias or same name. My question is, can a UNION be used for more than 2 tables?
I currently have 3 tables: orders(customerNum,orderNum,orderDate), orderline(orderNum,partNum),
part(partNum,description)
I want to display orderdate and ordernum, only if partNum= 1234 or customerNum= 123
everything is char datatype

I am able to innerjoin, however I am somewhat confused attempting a UNION query. is this possible?
so far

select orders.orderNum,orderDate

from orders,orderline

where orders.orderNum=orderline.orderNum and customerNum='xyz'

union

select orderline.orderNum,part.partNum

from orderline,part

where part.partNum=orderline.partNum and orderline.partNum='abcd'

order by 1

as you can see i am somewhat lost. At the moment the orderDate column is showing partnum and dates. But if someone can show how to make this possible I would greatly appreciate it.

View 6 Replies View Related

Advanced Union Query

Sep 6, 2004

Hi!

I'm trying to get the following query to work:

SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer
FROM item, t15
WHERE item.row_id = t15.id AND t15.f167 > getDate()
UNION ALL
SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id
+ '> KAN < / a > '
FROM item, t30
WHERE item.row_id = t30.id AND t30.f221 > getDate()
ORDER BY start DESC

The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.

So the question is basically, how do I get the following to work:

'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'

Vidar

View 6 Replies View Related

Problems With UNION-query

Jun 15, 2004

I have a sql statement with 2 SELECT statements combined with the keyword UNION. Something like this

SELECT a, b
FROM ... WHERE ...
UNION
SELECT c, d
FROM ... WHERE ...

If i execute this in the Query Analizer everything works fine and i get the results. But if i execute this through Visual Basic (with database.OpenRecordSet(sql) ) i get following error message:

The Microsoft Jet database engine cannot find the input table or query .... Make sure it exists and that its name is spelled correctly.

If i execute the first part of this combined query in VB (e.g. SELECT a, b FROM ... WHERE ...), no errors occur. The same with the second part.

I find it really strange and any hints and tips are welcome!
thanks in advance.

View 2 Replies View Related

Union Query Discrepancy

Jan 26, 2005

When I run the following query with a UNION:

SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCONLINETOOL.AMOUNT as PRICE, 15 AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCONLINETOOL.InYear, ITPSG.TBLCCONLINETOOL.InMonth, 'TRADITIONAL BOOKING' AS Reason FROM ITPSG.TBLCCONLINETOOL INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCONLINETOOL.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCONLINETOOL.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038' AND INYEAR = '2004'AND InMonth = 'OCTOBER' AND (ITPSG.TBLCCONLINETOOL.DESTINATION = 'DOMESTIC') AND (ITPSG.TBLCCONLINETOOL.TYPE = 'TRADITIONAL') UNION SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCEXCEPTIONS.PRICE as PRICE, ITPSG.TBLCCEXCEPTIONS.Lostsavings AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCEXCEPTIONS.InYear, ITPSG.TBLCCEXCEPTIONS.InMonth, ITPSG.TBLCCEXCEPTIONS.Reason FROM ITPSG.TBLCCEXCEPTIONS INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCEXCEPTIONS.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCEXCEPTIONS.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE (LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038') AND (ITPSG.TBLCCEXCEPTIONS.InYear = '2004') AND (ITPSG.TBLCCEXCEPTIONS.InMonth = 'OCTOBER') ORDER BY Reason

It returns these 16 records, 10 from the 1st table and 6 from the second. If I just remove the UNION operator and run them seperatly I get 11 from the 1st table and 6 from the second.

The record I am losing is the second of these two, but with the fields I am selecting they appear identical:
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING

Is there any reason why the UNION statement is making that second record vanish? Is there a way I can alter the statement so I can run the query with the UNION and not lose records?

Thanks,

View 2 Replies View Related

Union Query Difference

Oct 8, 2007

Guys can you tell me whether there is any difference between the following queries.......... Both of them are resulting same number of records. Not sure whether the output is same or not. Just wanted to know if result output wise if there is no difference than performance wise there might be some. Kinldy educate me on the same.

SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID
UNION
SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.PageBreakPositions= SCC.CollectionGID

SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID OR
FPO.PageBreakPositions= SCC.CollectionGID

Thanks,
Rahul Jha

View 14 Replies View Related