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 is
used in Analysis Services to create a dimension in a datastore. This
query tends to deadlock after about 10 days of running smoothly. Only
way to fix it is to reboot the box, I can recycle the services for a
quick fix but that usually only works for the next 1-2 times I call the
view.

This view is used to create a breakdown of the bill-to locations from
Continent-Global Region-Country-Sub Region-State/Province- City-Zip
Code

Yes, 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 set
the Isolation level to Repeatable Read for a view?

Here is the view code:

CREATE View dbo.vwBillTo
as


-- US ZipCode
Select '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 z
innerjoin dbo.FACT_SALES f
onz.ZipCode_WK=f.Bill_To
Wherez.US_Region_wk IS NOT NULL
Groupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrt

Union
--City
Select 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) as
varchar)+')'
,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 z
Wherez.US_Region_wk IS NOT NULL
Groupby z.State_Long,z.City,z.State_shrt,z.US_Region_wk

Union

-- Canada ZipCode
Select '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 z
innerjoin dbo.FACT_SALES f
onz.ZipCode_WK=f.Bill_To
Groupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrt

Union

--City
Select 'Parent'=z.Province_Long
,z.City as 'Child'
,z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as
'Child_ID'
Fromdbo.DIM_POSTAL_CODES_CAN z
innerjoin dbo.FACT_SALES f
onz.ZipCode_WK=f.Bill_To
Groupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrt
Union

-- Canada Province
Select 'CANADA'
,Province_Long
,Province_Long
Fromdbo.DIM_POSTAL_CODES_CAN
Groupby Province_Long

Union
-- Country
Select t.Region_NK
,c.Country_Name
,c.Country_Name
Fromdbo.DIM_COUNTRY c
InnerJoin dbo.DIM_WORLD_REGION t
Onc.Region_WK=t.Region_WK
Wherec.Country_Name Is Not Null
Group by t.Region_NK, c.Country_Name

Union

-- SubRegion
Select c.Country_Name
,sr.US_Region_Name
,sr.US_Region_Name
Fromdbo.DIM_US_REGION sr
InnerJoin dbo.DIM_COUNTRY c
Onsr.Country_wk=c.Country_WK
Groupby c.Country_Name, sr.US_Region_Name

Union
--Region
Select sr.US_Region_Name
,c.State_Long
,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'
Fromdbo.DIM_US_REGION sr
InnerJoin dbo.DIM_POSTAL_CODES_US c
Onsr.US_Region_WK=c.US_Region_WK
Groupby sr.US_Region_Name, c.State_Long,c.US_Region_wk

Union
-- Continent
Select Null
,Region_NK
,Region_NK[color=blue]
>From dbo.DIM_WORLD_REGION[/color]
WhereRegion_NK Is Not Null

View 1 Replies


ADVERTISEMENT

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

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

T-SQL (SS2K8) :: Deadlock When Creating New FK Constraint?

Sep 17, 2015

The other day developer asked me to create a new table that had a FK constraint to an existing largish table on the largish table's primary key.

When i ran the script, it took it about 4 seconds to complete, and in the meantime i had 3 deadlocks show up from other processes attempting to use the table the FK was being created from. The new table was empty at this point.

I've never seen this behavior before so I'm curious if creating a new FK constraint will cause a table lock on the source table for the FK.

View 1 Replies View Related

A Deadlock When Selecting From A View With Replicated Tables.

Dec 15, 2006

I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?

SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)

I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.

Thanks a bunch.

View 1 Replies View Related

Creating A 4-4-5 Time Dimension

Aug 29, 2007

Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?

I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.

Thank you!

View 4 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

CREATING ROLE-PLAYING DIMENSION

Dec 25, 2007

how to create a role playing dimension in SSAS. Like If I have a period dimension table with a key named as per_key and fact table which has keys named as from_per_key and to_per_key .Now both the fact table keys need to be connected to the Period dimension per_key.

gunja

View 3 Replies View Related

SQL 2012 :: Creating Dimension From Fact Table?

Dec 19, 2014

I have a fact table with few flag columns.

What is the best way to bring them to dimension?

Do I need to create dimension(dummy) from fact table for each flag or all flags in single dimension?

View 0 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

Creating Report Based On Parent-child Dimension

Nov 17, 2005

Hi

View 43 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

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 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

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

Deadlock Within Select?

Feb 8, 2006

My SQL Server has kicked out a deadlocked process, which should only berunning a select statement, though there is another select on one ofthe tables in the WHERE clause (see code below). Can anyone tell mewhether this is possible, or is it that my system, which is re-usingconnections, is trying to complete an earlier statement? I've lookedthrough the system and think I'm committing all transactions.The query I'm running (simplified, I don't use daft names like 'table1'or 'date_col', honest :) is:SELECT table1.*, table2.*, table3.*FROM table1, table2, table3WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2AND table1.col3 = 'xyz'AND (table1.date_col >= '2006-02-01' OR table1.date_col IN(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3= 'xyz'))ORDER BY table1.date_colThanksJ

View 2 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 On A Select Query, Possible?

Jan 31, 2005

I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:

Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?

If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.

Can anyone explain this? Or should I bounce my server and hope it never happens again?

Thanks,

Dave

View 1 Replies View Related

Analysis :: Creating OLAP Cube / Dimension In Visual Studio ERRORS

May 15, 2015

When I want to create a dimension i always end showing up errors below:

COPY
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"

[Code] ...

Errors and Warnings from Response
    Internal error: The operation terminated unsuccessfully.
    The following system error occurred:
    Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'DB LAB2', Name of 'DB LAB2'.

[Code] .....

View 2 Replies View Related

Transact SQL :: Creating Dummy Headers (column Names) With Union All

May 19, 2015

I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....

COL 1     COL2      COL3       COL4 etc.                         BEH                             BIG       BPL etc.

*************************************   INTAKT       DEFEKT       INTAKT DEFEKT          INTAKT

*************************************       B                E                 B         E                    B

I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?

View 4 Replies View Related

Deadlock -select With Exclusive Lock

May 12, 2008

Hi ,
I have some issues with deadlock.I am getting deadlock becuase of two
select on same table. The sql server log is like this ,
Select statements should have always shared lock.I am not getting why
its doing exclusive lock and creating deadlock.


5/6/2008 12:38 spid4s Unknown Deadlock encountered €¦. Printing
deadlock information
5/6/2008 12:38 spid4s Unknown Wait-for graph
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:1
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594050117632 (8d036f07c58f)
CleanCnt:3 Mode Flags: 0×0
5/6/2008 12:38 spid4s Unknown Grant List 3:
5/6/2008 12:38 spid4s Unknown Owner:0×12E9F160 Mode: S Flg:0×0 Ref:1
Life:00000001 SPID:68 ECID:0 XactLockInfo: 0×353D1C54
5/6/2008 12:38 spid4s Unknown SPID: 68 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×2FBB67F0 Mode: X SPID:112 BatchID:0 ECID:0 TaskProxy0×0792E378)
Value:0×38baa20 Cost0/11888)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:2
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594049986560 (6f02e1cd37c3)
CleanCnt:3 Mode:X Flags: 0×0
5/6/2008 12:38 spid4s Unknown Wait List:
5/6/2008 12:38 spid4s Unknown Owner:0×12396EE0 Mode: S Flg:0×2 Ref:1
Life:00000000 SPID:90 ECID:0 XactLockInfo: 0×0AA8178C
5/6/2008 12:38 spid4s Unknown SPID: 90 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×353D1C30 Mode: S SPID:68 BatchID:0 ECID:0 TaskProxy0×13B3E378)
Value:0×12e9e780 Cost0/6164)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:


If you have any idea regarding this please let me know ASAP.


Thanks in advance .

View 4 Replies View Related

SQL Server 2000 Deadlock Concurrent Select Transcations

Oct 14, 2007

There are a series of traces of transactions for essentially the same units of work. Each includes a retrieving the same table "Select" from the database. The first 4 transactions ran concurrently. The last one ran about 7 minutes later with no other concurrent transactions.

The elapsed times for the database accesses for each of the first 4 is significantly higher than the last one.
Can you think of any database optimazation that might improve this?


Are there any utilities available than can be used for stand-alone testing of SQL Server database performance?
E.G. they would measure response time for reading/writing large/small amounts of canned data once/multiple times with concurrent/nonconcurrent access.

SQL Server Database is on 2000.I am not sure about the ISOLATION level in the Java code.

Basically I am new to SQL Sever....Please ans the above questions and provide me the direction to proceed on these questions

View 5 Replies View Related

SELECT UNION SELECT Condition

Dec 14, 2006

let say i got such condition

INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)

do you think that mssql will produce error or problem?
from what i heard it will.

View 1 Replies View Related

Sum A Union Select

Mar 21, 2008

Hi all
I'm new to SQL Server 2005 and its queries. I've created a DB that stores bonusses. For example, a person goes out with usually 4 other people, and does their work. The first person is the engineer, the second is the team leader, and the third and forth is the helpers. According to the number of notes they capture for that day, they get a bonus. if 3 notes has been done for the day, the enigeer gets 100 bucks per note, aka 300 bucks - 3 * 100 = 300. the team leader gets half of it, 150, and the helpers get each half of that, 75 and 75.
 in a select query i did the match/calculation
now i want to add everything up to get a total, 300+150+75+75 - how does one do it, and write it to a table? Note: The amount of people can change, the role of the person can change, so a helper can be a team leader or engineer as well, and the amount also changes depending on performance.DECLARE @BonusID int;
SET @BonusID = '1';


SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, b.Notes * a.Amount AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Surveyor')

UNION

SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, (b.Notes * a.Amount)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Team Leader')

UNION

SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, ((b.Notes * a.Amount)/2)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Helper')

ORDER BY Total DESC

  










BonusDetailID
BonusID
Name
Surname
Role
Total
DateModified

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

1
1
Riaan
de Lange
Surveyor
300
2008/03/21 14:17

2
1
Kobus
Vermaak
Team Leader
150
2008/03/21 14:17

3
1
Johan
Bester
Helper
75
2008/03/21 14:17

4
1
Pieter
Koen
Helper
75
2008/03/21 14:17 
 
How do i get the total for the bonusid = 1? which should be 600

View 6 Replies View Related

Union Select

Sep 9, 2006

I want to include product added date and time in my querry but getting this error "The number of columns in the two selected tables or queries of a union query do not match".


Code:

SELECT Products.*,ProdPics.* FROM Products INNER JOIN ProdPics ON Products.ItemID=ProdPics.ItemID WHERE
Products.ItemID = 4 UNION SELECT Date, Time FROM History WHERE ProdID = 4



What am i doing wrong ?

View 2 Replies View Related

IF SELECT UNION

Jul 23, 2005

Using SQL 2000...tblCustomer:CustomerID intCompanyName varchar(20)HasRetailStores bitHasWholesaleStores bitHasOtherStores bittblInvoiceMessages:MessageID intMessageText varchar(100)CustomerID intAllRetailStores bitAllWholesaleStores bitAllOtherStores bitAllStores bitIsActive bitThe Invoice Messages are text blocks which will be added to invoicesgoing out to customers. A customer can have Retail stores, Wholesalestores, and/or Other Stores. The messages can go to only thosecustomers with a specific type of store, or all customers, or to aspecific customer. It is important to note that a customer can have 1,2 or all 3 types of stores. Here are a couple of sample entries in theinvoice messages table:tblInovoiceMessages1,For Customers with Retail and Wholesale Stores,0,1,1,0,02,Only For Customer # 10,10,0,0,0,0....Attempt #1 (IF SELECT UNION SELECT)IF (SELECT TC.HasRetailDestinationsFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)Attempt #1 checks if the Customer has retail stores, and if it does,returns all messages for Retail Stores. The second Select statementchecks for all messages designated for that particular Customer. I useUnion to combine the tables (which have identical structures) and itworks great.Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)IF (SELECT TC.HasRetailStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)UNIONIF (SELECT TC.HasWholesaleStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)Attempt #2 is the same as Attempt#1 except that I attempt to Unionanother If Select query to the first two queries. This attemptgenerates:Server: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'IF'.I have tested each individual If Select statement, and they all returnproper results. However, anytime I attempt to Union more than 1 IfSelect statement together, I get the Msg 156 error. Is there somelimitation that I am not aware of?

View 4 Replies View Related

Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2         b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 1 Replies View Related

Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2          b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 3 Replies View Related

Bout Union Select....

Jan 25, 2007

can someone help me how can i access datas using union? or show my data in the gridview.....pls.....
coz i have 3 tables...
i need to output  the datas of the 3 tables in 1 gridview.
tables are: TT0001,TM0011,TM0001
TT0001 has syain_id(PK),time_in,time_out,year(PK),month(PK),day(PK)
TM0011 has office_name,office_id(PK)
TM0001 has syain_id(PK) office_id(PK),empl_date
the scenario is:
i have a combo box for office_name, and a textbox for imputting the date( 2007/01/23).when i click the button OK.
the time_in,time_out and syain_name of the person who is present in the choosen date(ex. 2007/01/23)  will be shown in my gridview_info.
i already have some codes but  it still confuse me and has many errors....
my code: 
GridView_info.Visible = True
        '//for odbc        Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes"        Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)
        'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
        MyConn.Open()
        '//parsing 2        Dim MyString As String = TextBox_date.Text        Dim MyDateTime As DateTime = DateTime.Parse(MyString)        Console.WriteLine(MyDateTime)
       Dim stringQuery2 As String = "SELECT * from tempo_db"
       Dim SQLcommand2 As New Odbc.OdbcCommand("Create table tempo_db ( syain_name char(40),year char(4), month char(2), day char(2), in_hh int, in_mi int, out_hh int, out_mi int )", MyConn)        SQLcommand2.ExecuteNonQuery()
        Dim SQLcommand1 As New Odbc.OdbcCommand("Drop table tempo_db", MyConn)        SQLcommand1.ExecuteNonQuery()
        Dim da As New Odbc.OdbcDataAdapter("Select TT0001.Year,TT0001.Month,TT0001.Day,TT0001.in_hh,TT0001.in_mi,TT0001.out_hh,TT0001.out_min where TT0001.Year =" + MyString + " and TT0001.Month =" + MyString + " and TT0001.Day =" + MyString + " and TT0001.syain_id =" + TM0001.syain_id + " Union Select TM0001.syain_name where TM0001.syain_id =" + TT0001.syain_id + "  Union Select TM0011.office_name where TM0011.office_id =" + TM0001.office_id + "        Dim ds As New DataSet()        Dim foundrow As DataRow        Dim ds2 As New DataSet        'Dim temp_data_table As New DataTable
        GridView_info.DataSource = ds        da.Fill(ds, "TT0001")
        Dim sqldataadapter2 As New Odbc.OdbcDataAdapter(stringQuery2, MyConn)        sqldataadapter2.Fill(ds2, "tempo_db")        Dim date_ctr As Integer        date_ctr = 1
        While date_ctr <= Date.DaysInMonth(Now.Year, Now.Month)
            ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Year")}            'ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Month")}            'ds.Tables(2).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Day")}
            foundrow = ds.Tables(0).Rows.Find(date_ctr)            'foundrow = ds.Tables(1).Rows.Find(date_ctr)            'foundrow = ds.Tables(2).Rows.Find(date_ctr)
            Dim in_hh, in_mi, out_hh, out_mi As String
            If foundrow IsNot Nothing Then                in_hh = foundrow.Item("in_hh")                in_mi = foundrow.Item("in_mi")                out_hh = foundrow.Item("out_hh")                out_mi = foundrow.Item("out_mi")            End If
        End While
        GridView_info.DataSource = ds2        GridView_info.DataBind()        MyConn.Close()
    End Sub
 
/// ps: im having problem with my select statements.....
can someone help me analyze what i had written... and what is my mistake...
 
any help is greatly appreciated....
 thanks
nat!
 

View 1 Replies View Related

ADO.NET && SELECT ... UNION Issue???

Feb 3, 2007

Hi,
I have discovered something weird.
I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.
In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.
And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)

View 2 Replies View Related







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