Help with (easy for you) query

  • Awesome...I see the error in my way..

    Thanks everyone!

    Good karma to you both.

  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 ')'.

  • 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

  • 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 ')'.

  • That's a syntax error somewhere. Check your parens.

  • ...I'm checking...moved, added, deleted parens....can't seem to get it..

  • 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

  • phil..I added a select in between the begin and end....and got incorrect syntax near the keyword 'begin'

  • I got it..

    thanks again everyone...!

  • 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

    )

  • works Perfect...thanks Steve

  • 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