April 26, 2011 at 1:41 pm
I have this query, but I can't get it to work.
The error I get is
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
declare @cnt smallint
set @cnt =
select *, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105)
IF @cnt > 1
BEGIN
April 26, 2011 at 1:49 pm
What are you trying to set @cnt to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 1:49 pm
I would advice you to use Aliases for the tables and then run the query.. probabaly some outer query is accessing the columns of the inner query..
April 26, 2011 at 1:53 pm
ColdCoffee (4/26/2011)
I would advice you to use Aliases for the tables and then run the query.. probabaly some outer query is accessing the columns of the inner query..
It's likely this piece
set @cnt = select *, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 1:54 pm
Hi Gail
I need to email notification if there are any records returned from that query.
So once the top part is working I need to do something like -
If @cnt > 1
BEGIN
EXEC msdb.dbo.sp_send_dbmail
April 26, 2011 at 1:57 pm
Try this little change:
Instead of:
declare @cnt smallint
set @cnt =
select *, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
Do
declare @cnt smallint
Select @cnt = count(*)
, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 1:58 pm
The original query works fine -
This statement -
select distinct glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105)
I added the cnt part so I could send an email if the record count is greater then zero
April 26, 2011 at 2:01 pm
SQLRNNR -
I made your changes and got the error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'EM'.
April 26, 2011 at 2:02 pm
When trying to set a variable, you have to be careful in whether you use set or select. You cannot call multiple columns in the Set, where in the select you can.
And actually, you should declare a variable for each column in this case if you want multiple columns in the same statement since data retrieval and variable assignment can't be combined in that manner.
declare @cnt int
,@glaccount varchar(25)
,@ErrorReason varchar(50)
set @cnt = count(*),@glaccount = glaccount, @errorreason = 'ACCOUNT NOT FOUND'
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 2:03 pm
krypto69 (4/26/2011)
SQLRNNR -I made your changes and got the error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'EM'.
Post the entire query you just ran to get that error.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 2:09 pm
There's no need to count all the rows just to see if any rows exist. It's inefficient.
IF EXISTS (select 1
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105))
BEGIN
-- Rest of query here
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 2:11 pm
GilaMonster (4/26/2011)
There's no need to count all the rows just to see if any rows exist. It's inefficient.
Good point
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 2:14 pm
krypto69 (4/26/2011)
The original query works fine -This statement -
select distinct glaccount, 'ACCOUNT NOT FOUND' ErrorReasonfrom dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105)
I added the cnt part so I could send an email if the record count is greater then zero
Use @@ROWCOUNT to obtain the number of rows returned if you intend to use any of the returned data. Otherwise, use Gail's suggestion IF EXISTS.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 2:21 pm
declare @cnt smallint
Select @cnt = count(*)
, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105)
--print @cnt
Error is:
Msg 141, Level 15, State 1, Line 11
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
April 26, 2011 at 2:24 pm
krypto69 (4/26/2011)
declare @cnt smallint
Select @cnt = count(*)
, glaccount, 'ACCOUNT NOT FOUND' ErrorReason
from dynamicsgp_utilities.dbo.V_sfvouchers
where (sfid like 'EM-%' or sfid like 'EXP-REP%') and sfid not in
(
select pordnmbr from lpb..pm30200 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm20000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
UNION select pordnmbr from lpb..pm10000 where doctype = 1 and (pordnmbr like 'EM-%' or pordnmbr like 'EXP-REP%')
)
and glaccount not in (select actnumst from lpb..gl00105)
--print @cnt
Error is:
Msg 141, Level 15, State 1, Line 11
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Yup - look at my follow-up post (right after the post containing that script) and also look at Gails follow up post.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply