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