SubQuery In Both SELECT And WHERE Part. How To Optimise?
Apr 11, 2008
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.
What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.
Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.
SELECT
f.FeedbackText AS [Feedback Comment],
u.Name AS [Feedback Author],
f.CreatedDate AS [Created On],
(
SELECT COUNT(*)
FROM FeedbackResponse fr
WHERE fr.FeedbackID = f.ID
) AS [Responses]
FROM Feedback f
INNER JOIN [User] u ON f.StaffID = u.StaffID
WHERE f.CreatedDate >= @DateFrom
AND f.CreatedDate <= @DateTo
AND
(
@HasResponses IS NULL
OR
(
@HasResponses = 1 AND
(
SELECT COUNT(*)
FROM FeedbackResponse fr
WHERE fr.FeedbackID = f.ID
) > 0
)
OR
(
@HasResponses = 0 AND
(
SELECT COUNT(*)
FROM FeedbackResponse fr
WHERE fr.FeedbackID = f.ID
) = 0
)
)
View 2 Replies
ADVERTISEMENT
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Apr 3, 2008
Hi,
I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.
I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.
Thanks,
Chamal.
View 6 Replies
View Related
Jul 23, 2005
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD
View 2 Replies
View Related
Dec 24, 2007
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
a b
----------------------------
tom small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
View 4 Replies
View Related
Mar 22, 2008
1) Getting this error:
Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near the keyword 'in'.
Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near ','.
Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason...
Here is my procedure.
alter procedure SP_ImportHotels_Step4
As
BEGIN
INSERT INTO classifieds_Photos
( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL )
SELECT classifieds_Ads.Id,
[i.ID] in (Select Min(Images.ID) from Images group by HotelID),
GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads
INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID)
INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID;
END
go
execute SP_ImportHotels_Step4
View 15 Replies
View Related
Feb 19, 2001
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned in second
View 1 Replies
View Related
Feb 19, 2001
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp ---------------
aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name,
and concetanate to query,
and execute with exec
but in this way you cant set to my int variable
please help me thanks for all
hra
View 1 Replies
View Related
Jul 4, 2006
I the following table:
table1
member_name legacy_id team_name
-----------------------------------------
Bill 1234 nationals
Bill 1234 nationals
Tom 3456 nationals
Tom 3456 orioles
I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.
I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.
My query should return
team_name count
-----------------
nationals 2 - because it occurs for bill, and tom
orioles 1 - because it occurs for tom
If I do something like:
select
distinct(team_name),
count(team_name)
from table1
group by team_name
I get back:
team_name count
-------------------
nationals 3 - because it occurs for bill twice, and tom once
orioles 1 - because it occurs for tom once
I've tried something like:
select
team_name,
count(team_name)
from
table1
where legacy_id in (
select distinct legacy_id from table1
)
I get a syntax error. Regardless, I'm not sure this will give me what I need.
I've tried over a dozen variations of select distinct, joins, etc but with no luck.
Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.
View 4 Replies
View Related
Nov 19, 2006
Hello!
I can use querys like these in Access:
SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
-Teemu
View 3 Replies
View Related
Dec 5, 2013
How do I only display a part of data from the column ?
Lets say emp_id is E3456 and all i want to display is 3456 without that E?
View 2 Replies
View Related
Dec 23, 2013
I'm trying to form a query that will select part of a result.I'm trying to take out the ending of results that end in '-PR'.
Example: The original result is 'Jbbx32-PR'. I want to select it as 'Jbbx32'.
View 5 Replies
View Related
Feb 20, 2014
I have a PURCHASE ORDER NUMBER which is character 24 pos. and I only want to select in the query those which start with
'101' from 1 to 3 = '101'
How to do this in the query?
View 5 Replies
View Related
Oct 28, 2006
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
View 2 Replies
View Related
Apr 1, 2008
Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
View 4 Replies
View Related
Nov 28, 2006
Hi there,
I am pulling back records from the DB in this case to get Wheel information. I am pulling back based on user input, but also need to query a second table that contains the Price and model number from another table based on a field being pulled back in the original select.
I am not sure if this makes sense, here is a working copy of the SQL I have , but it's not pretty. There must be another way of stating this statement that i am missing, can anyone give me some suggestiosn?
SELECT tblMacPak2.*,
(SELECT ListPrice
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS ListPrice,
(SELECT PartNumber
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS PartNumber
FROM tblMacPak2
WHERE (Make = N'honda') AND (Model = N'civic') AND (SubModel = N'standard') AND (YearRange = N'2006') AND (Factory_Wheel_Diameter = N'15')
3 selects in one statement...that can't be right.
Thanks,
View 5 Replies
View Related
Apr 1, 2008
Hi.
I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?
View 6 Replies
View Related
Jun 26, 2006
I have a select such as this:
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
Now, I want to order the result set by COLUMNS. When I try the following query, the SQL fails.
select * from
(
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
) as T
order by COLUMNS
Any idea how this can be done?
Thanks
View 4 Replies
View Related
Jun 16, 2004
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View 1 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Feb 18, 2014
I have an xml stored in a coulum of a table and I use the following query to extract an xml element :
select CONVERT(XML,CONVERT(NVARCHAR(max),Response)).value('(/Quote/error)[1]','nvarchar(max)') as Excepiton .The result of the expression is :
TL43:The product has no marked price.;I would like to select only the code : TL43and then separately I would like to select The product has no marked price.Is there a way I can do it?
View 1 Replies
View Related
Sep 12, 2014
There is column mobile_number in my table
I have to replace with star(*) to the middle six character from contact number of 10 digit.
For example
The Contact number is 9334459875
I have to display it as follows
93******75 first two digit and last two only.
View 1 Replies
View Related
Mar 6, 2008
Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:
Table: Profiles
Fields: Id, ProfileName
Table: Groups
Fields: Id, GroupName, ProfileId
I then need to return something like this:
ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"
Does anyone know how this can be done?
Thanks!
View 7 Replies
View Related
Apr 12, 2008
I'm trying to select from a table with three columns. I want these columns to be spread out among multiple columns based on the values. I hope someone can shed some light on this. I might be able to use pivot, but don't know how the syntax would roll for this.
Here is the example of dummy values and the output I am trying to obtain.
drop table table1
create table table1
(Category int, Place int, Value int)
insert into table1 values
(1, 1, 20)
insert into table1 values
(1,2, 12)
insert into table1 values
(1,3, 30)
insert into table1 values
(2,1, 34)
insert into table1 values
(2,2, 15)
insert into table1 values
(2,3, 78)
select Category,
(select top 1 value from table1 where place = 1 and Category = t1.Category) as place1,
(select top 1 value from table1 where place = 2 and Category = t1.Category) as place2,
(select top 1 value from table1 where place = 3 and Category = t1.Category) as place3
from Table1 t1
group by Category
Thanks for the help.
View 5 Replies
View Related
Apr 30, 2008
Hey guys, I have a brain buster for you today:
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255)
select e.ssn,
e.firstname,
e.lastname,
( @path = select coalesce(@path + ', ', '')
from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt
where s1.spaceID = 133225
select @path)
from employees e
where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
View 8 Replies
View Related
Aug 22, 2006
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
View 3 Replies
View Related
Jun 2, 2006
Hello All,
I have a query that I could use some help with. I would like to see how I could make this query more optimised. I am not hte best at Joins so if someone could help me with this that would be great. Right now its taking about 24-30 secs to run which is a no go. Im wondering if some kind of join would work better?
thank you much
-jes
SELECTrecipes_signed_up_for.user_id,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.use_in_lesson_plan AS use_in_lesson_plan,
recipes_signed_up_for.attached_lesson_plan AS attached_lesson_plan,
recipes_signed_up_for.participate,
recipes_signed_up_for.authorized AS authorized,
recipes_signed_up_for.date_signed_up_for AS date_signed_up_for,
users.user_id,
users.f_name AS f_name,
users.l_name AS l_name,
users.email_address AS email_address,
users.primary_phone AS primary_phone,
recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.ingredient_quantity AS ingredient_quantity,
recipe_ingredients.ingredient_unit,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
recipes_signed_up_for_ingredients_needed.ingredient_id,
recipes_signed_up_for_ingredients_needed.ingredient_needed AS ingredient_needed
FROMrecipes, recipe_ingredients, ingredients, recipes_signed_up_for_ingredients_needed, recipes_signed_up_for, users, locations, regions
WHERErecipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.user_id = #url.user_id#
AND recipes_signed_up_for_ingredients_needed.ingredient_id = recipe_ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.ingredient_needed = 1
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id = #URL.user_id#
AND recipes_signed_up_for.participate = 1
AND locations.region_id = regions.region_id
AND recipes_signed_up_for.recipe_id IN (SELECTrecipe_id
FROMrecipes
WHEREactive_recipe = 1)
ORDER BY recipes.recipe_name
View 9 Replies
View Related
Oct 11, 2005
Hi guysIs there any way I can run this query faster? Should I take out the ORDER BYclause? This is supposed to return 17,000 rows and takes around 30 minutes orso. Is there no way at all to get this result faster?select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.clientinitials, r.clientidno, r.grantedamount, r.bankname,r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,r.erfdescription, r.commenthistory, br.expectedregdatefrom bondtrak..rptdetail rjoin ebondprd..bankresponse bron br.applicationid = r.applicationidwherer.rundate = '20051010'and r.primarybankind = 'Y'and r.status = 'granted'and r.statusdate between '20020101' and '20050930'and r.businessunit in ('bond choice', 'ppl')order by r.sitename, r.consultantname, r.statusdateThanks for any help.Driesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 6 Replies
View Related
Jul 20, 2005
I have these 3 queries - they are the same except each fetches record countsfor one of 3 different record types, nSubsets (type 0), nAssets (type 1) andnImages (type 2). Is there any way I could get all 3 of these (based on theNode.Type integer) with a single query?IF @Error = 0BEGINSELECT @nSubsets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 0SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nAssets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 1SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nImages = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 2SET @Error = @ERROREND
View 5 Replies
View Related
May 29, 2008
hi,
I want to know how to optimise this query, the results almost reach 6000 rows, I want to speed this query.
condition2=Select Distinct A,B,B+C+D,E,F,G From a.table where condition1 order by A,B,B+C+D,E,F,G
Select Sum(amount) From b.table where condition2 And date=datetime.
Thanks in advance.
View 1 Replies
View Related
Apr 18, 2008
Hi,
I use this piece of code in a trigger to check if new entered row has unique order number in the given year:
IF EXISTS
(
SELECT COUNT(*)
FROM SPD_Orders
GROUP BY ORD_Year, ORD_Number
HAVING COUNT(*) <> 1
)
RAISERROR('Order with this No exists in given year, 11, 1)
but this code causes deadlocks in my database.
Any suggestions how can I change it to avoid these deadlock?
1. Some constrain? If yes, so how to write it?
2. Index like to enable query optimiser to change execution plan?
CREATE NONCLUSTERED INDEX [_dta_index_SPD_Orders_10_1429580131__K8_K7] ON [dbo].[SPD_Orders]
(
[ORD_Year] ASC,
[ORD_Number] ASC
3. Same index as above but unique and then I will not have to use this trigger, cause database itself with return an error?
4. Any other, the best solution ?
Please help.
Thanks.
Przemo
View 4 Replies
View Related
Nov 7, 2007
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
View 4 Replies
View Related