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

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

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

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

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

Select Without Subquery

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

Select From Subquery

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

Select Subquery Returned More Than 1 Value

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

SQL Problem With Subquery In Select

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

How Do I Select Tablename In Subquery

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

How Do I Select Tablename In Subquery

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

SELECT DISTINCT Subquery

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

Subquery In SELECT Statement Before FROM

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

Select And Display Part Of Column

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

SELECT Part Of A String In A Column

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

How To Select Partial Part Of Column

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

Select Subquery To Return COUNT

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

Insert Query With A Select Subquery

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

Subquery Issues In A Select Statement

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

Insert Query With A Select Subquery

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

Ordering A UNION Select With Subquery

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

Using User Input As Part Of A SELECT Statement

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

Return Variable Name As Part Of Select Statement.

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

SQL Server 2012 :: Select A Part Of Text From The XML?

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

How To Select Part Of Mobile Number From Digit

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

Subquery With Multiple Rows Inside SELECT

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

Subquery, Select Case, Pivot Help. Is There An Easier Way?

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

Select Query With Multiple Row Coalesce Subquery

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

Excluding Part Of Select Statement If No Data Is Returned In Results

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

Query Optimise

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

Any Way To Optimise This Query?

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

Optimise These 3 Queries Into One?

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

How To Optimise This Query?

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

How To Optimise This Code?

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

Error: Only One Expression Can Be Specified In The Select List When The Subquery Is Not Introduced With EXISTS.

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







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