April 26, 2011 at 2:33 pm
Awesome...I see the error in my way..
Thanks everyone!
Good karma to you both.
April 26, 2011 at 2:43 pm
You're welcome and good luck
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 27, 2011 at 7:31 am
The original requirement was if there's more than one row, do X. If Exists will test if there is at least one row. Are you sure that's what's needed?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2011 at 7:47 am
GSquared (4/27/2011)
The original requirement was if there's more than one row, do X. If Exists will test if there is at least one row. Are you sure that's what's needed?
Good point, but I suspect that was an error in the original query, as the OP later clarified
I need to email notification if there are any records returned from that query.
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 27, 2011 at 8:19 am
I spoke too soon..
when I run gails script
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
--------------------------------------------------------------------------------
I get error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
April 27, 2011 at 8:22 am
krypto69 (4/27/2011)
I spoke too soon..when I run gails script
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
--------------------------------------------------------------------------------
I get error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
I don't see an error with the query she provided. Please show us how you integrated her query with yours.
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 27, 2011 at 8:24 am
I spoke too soon..
when I run gails script
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
--------------------------------------------------------------------------------
I get error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
April 27, 2011 at 8:32 am
That's a syntax error somewhere. Check your parens.
April 27, 2011 at 8:33 am
...I'm checking...moved, added, deleted parens....can't seem to get it..
April 27, 2011 at 8:38 am
I think you need to put something between the BEGIN and END - not just a comment.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 27, 2011 at 8:51 am
phil..I added a select in between the begin and end....and got incorrect syntax near the keyword 'begin'
April 27, 2011 at 8:54 am
I got it..
thanks again everyone...!
April 27, 2011 at 8:59 am
strange. It's almost like you're running something else in that batch that causes an error.
Does this work?
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
)
April 27, 2011 at 1:13 pm
works Perfect...thanks Steve
April 27, 2011 at 1:37 pm
krypto69 (4/27/2011)
I got it..thanks again everyone...!
Good to hear.
You're welcome.
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 - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply