Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Question about OR or IN

Expand Messages
  • MELVYN LOPEZ
    Are you aware if there is an index on the trncode column? Using IN should work: AND (agi.trncode in ( Sales Amount , Financial Credit )) ALSO, you may have to
    Message 1 of 22 , Apr 29, 2008
    • 0 Attachment
      Are you aware if there is an index on the trncode column?
      Using IN should work:

      AND (agi.trncode in ('Sales Amount','Financial Credit'))

      ALSO, you may have to scan your code to make sure all BEGINs are properly ENDed.

      MELVYN PATRICK

      John Warner <john@...> wrote: ...
      "I have tried using IN to the same effect, goes away."

      Thanks for the reply though

      John Warner

      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of A.J. Morales
      > Sent: Tuesday, April 29, 2008 8:36 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] Question about OR or IN
      >
      >
      > how about:
      >
      > AND (agi.trncode in ('Sales Amount','Financial Credit'))
      >
      > AJ
      >
      > SQL Server 2K on reasonably beefy hardware
      >
      > I have a rather lengthy and elaborate query in a stored proc
      > that runs in a reasonable amount of time (couple of minutes or so)
      >
      > If I make a change in one of the Sub Queries the thing times
      > out in a VBA app that actually calls the proc. Within the ADO
      > I am set for 11 minutes on the time out. Ok so the change:
      >
      > In the original I have this in a WHERE clause
      >
      > AND agi.trncode = 'Sales Amount'
      >
      > Again with just this simple test on this field all is well
      > but if I add this
      >
      > OR agi.trncode = 'Financial Credit'
      >
      > the query goes away. The OR here is not doubling the amount
      > of rows that are later compared to other things etc.
      >
      > I have tried using IN to the same effect, goes away. Any
      > ideas on how to include rows that match the OR condition and
      > not take a huge performance hit? I can post the entire query,
      > but most of it will not make any sense as the database is
      > dozens of tables etc. There is no secret here so if someone
      > thinks it will help trouble shoot just shout. As to giving
      > you DDL, that is not a practical solution here.
      >
      > Thanks
      >
      > John Warner
      >
      >
      >
      > __________________________________________________________
      > ______________________
      > Be a better friend, newshound, and
      > know-it-all with Yahoo! Mobile. Try it now.
      > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >






      ---------------------------------
      Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

      [Non-text portions of this message have been removed]
    • jigglypsyduck
      Hi John, Were you able to resolve your problem? For your second question, why not do a left outer join from Sales to Cost? Could you send a copy of the from
      Message 2 of 22 , Apr 30, 2008
      • 0 Attachment
        Hi John,

        Were you able to resolve your problem?

        For your second question, why not do a left outer join from Sales to
        Cost?


        Could you send a copy of the from and where clause?

        Thanks,
        An


        --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
        <john@...> wrote:
        >
        > Yes, I found one of the other parts of my where was not being applied
        until
        > after the OR or IN, a bit of re-arranging helped solve that.
        >
        > Another question, guess I ought to change the header, but I'm lazy
        today.
        >
        > Same query, it UNION ALL to sub queries regarding Sales and Costs.
        I have
        > found that there are a couple of rows (among hundreds) of sales that
        don't
        > have matching costs. What I mean is to the Cost sub query this
        transactions
        > literally don't exist, not just this field or that is NULL, the row simply
        > does not exist. But on the Sales side a row does exist. Is there
        anyway to
        > get a row somehow created for the cost side. My Sales row seems to
        be
        > dropping out due to the lack of a match when the UNION occurs? Any
        > suggestions for a solution?
        >
        > John Warner
        >
        >
        >
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
        Michael Weiss
        > > Sent: Tuesday, April 29, 2008 11:43 AM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: RE: [SQLQueriesNoCode] Question about OR or IN
        > >
        > >
        > > Are you able to view the query plan, John?
        > >
        > >
        > >
        > > _____
        > >
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John
        Warner
        > > Sent: Tuesday, April 29, 2008 5:55 AM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: RE: [SQLQueriesNoCode] Question about OR or IN
        > > Importance: Low
        > >
        > >
        > >
        > > ...
        > > "I have tried using IN to the same effect, goes away."
        > >
        > > Thanks for the reply though
        > >
        > > John Warner
        > >
        > > > -----Original Message-----
        > > > From: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%
        40yahoogroups.com>
        > > yahoogroups.com
        > > > [mailto:SQLQueriesNoCode@
        > > <mailto:SQLQueriesNoCode%40yahoogroups.com>
        > > yahoogroups.com] On Behalf Of A.J. Morales
        > > > Sent: Tuesday, April 29, 2008 8:36 AM
        > > > To: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%
        40yahoogroups.com>
        > > yahoogroups.com
        > > > Subject: Re: [SQLQueriesNoCode] Question about OR or IN
        > > >
        > > >
        > > > how about:
        > > >
        > > > AND (agi.trncode in ('Sales Amount','Financial Credit'))
        > > >
        > > > AJ
        > > >
        > > > SQL Server 2K on reasonably beefy hardware
        > > >
        > > > I have a rather lengthy and elaborate query in a stored proc
        > > > that runs in a reasonable amount of time (couple of minutes or so)
        > > >
        > > > If I make a change in one of the Sub Queries the thing times
        > > > out in a VBA app that actually calls the proc. Within the ADO
        > > > I am set for 11 minutes on the time out. Ok so the change:
        > > >
        > > > In the original I have this in a WHERE clause
        > > >
        > > > AND agi.trncode = 'Sales Amount'
        > > >
        > > > Again with just this simple test on this field all is well
        > > > but if I add this
        > > >
        > > > OR agi.trncode = 'Financial Credit'
        > > >
        > > > the query goes away. The OR here is not doubling the amount
        > > > of rows that are later compared to other things etc.
        > > >
        > > > I have tried using IN to the same effect, goes away. Any
        > > > ideas on how to include rows that match the OR condition and
        > > > not take a huge performance hit? I can post the entire query,
        > > > but most of it will not make any sense as the database is
        > > > dozens of tables etc. There is no secret here so if someone
        > > > thinks it will help trouble shoot just shout. As to giving
        > > > you DDL, that is not a practical solution here.
        > > >
        > > > Thanks
        > > >
        > > > John Warner
        > > >
        > > >
        > > >
        > > >
        __________________________________________________________
        > > > ______________________
        > > > Be a better friend, newshound, and
        > > > know-it-all with Yahoo! Mobile. Try it now.
        > > > http://mobile.
        > > <http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ>
        > > yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
        > > >
        > > > ------------------------------------
        > > >
        > > > Yahoo! Groups Links
        > > >
        > > >
        > > >
        > >
        > >
        > >
        > >
        > > __________ Information from ESET Smart Security, version of
        > > virus signature database 3062 (20080429) __________
        > >
        > >
        > > The message was checked by ESET Smart Security.
        > >
        > >
        > > http://www.eset.com
        > >
        > >
        > >
        > > __________ Information from ESET Smart Security, version of
        > > virus signature database 3063 (20080429) __________
        > >
        > > The message was checked by ESET Smart Security.
        > >
        > > http://www.eset.com
        > >
        > >
        > >
        > > [Non-text portions of this message have been removed]
        > >
        > >
        > > ------------------------------------
        > >
        > > Yahoo! Groups Links
        > >
        > >
        > >
        >
      • John Warner
        I m getting an error Incorrect syntax near ) This is the code: sum(case when agi.glacctcode = 000-1210-00 then 0 else case when agi.drcr = CR
        Message 3 of 22 , May 1, 2008
        • 0 Attachment
          I'm getting an error " Incorrect syntax near ')' "

          This is the code:

          sum(case when agi.glacctcode = '000-1210-00' then 0 else
          case when agi.drcr = 'CR' then -agi.glamount else agi.glamount end) AS
          Amount

          Clearly I'm doing something wrong, but the things I've tried are not
          resolving it. What is the proper way to nest a CASE?

          In case it isn't clear what I'm after is if the account code is
          '000-1210-00' then set the Amount field for this row = 0 else test drcr, if
          'CR' then negate amount else leave it alone for the sum function.

          Thanks


          (SQL Server 2K)

          John Warner
        • Tim Mitchell
          John, from what I can tell you don t need to nest a case statement here - just make it a single multipart case statement. Use something like: SELECT SUM(CASE
          Message 4 of 22 , May 1, 2008
          • 0 Attachment
            John, from what I can tell you don't need to nest a case statement here
            - just make it a single multipart case statement. Use something like:



            SELECT SUM(CASE WHEN agi.glacctcode = '000-1210-00' THEN 0

            WHEN agi.drcr = 'CR' THEN -agi.glamount

            ELSE agi.glamount END)

            AS Amount



            Hope this helps...

            Tim



            ________________________________

            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
            Sent: Thursday, May 01, 2008 8:22 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] Syntax error nested CASE
            Importance: Low



            I'm getting an error " Incorrect syntax near ')' "

            This is the code:

            sum(case when agi.glacctcode = '000-1210-00' then 0 else
            case when agi.drcr = 'CR' then -agi.glamount else agi.glamount end) AS
            Amount

            Clearly I'm doing something wrong, but the things I've tried are not
            resolving it. What is the proper way to nest a CASE?

            In case it isn't clear what I'm after is if the account code is
            '000-1210-00' then set the Amount field for this row = 0 else test drcr,
            if
            'CR' then negate amount else leave it alone for the sum function.

            Thanks

            (SQL Server 2K)

            John Warner





            [Non-text portions of this message have been removed]
          • John Warner
            Thanks, knew some one would see through my blindness. I ll give it a shot and shout back if it fails (which I doubt). Morning brain fart on my part. Glad you
            Message 5 of 22 , May 1, 2008
            • 0 Attachment
              Thanks, knew some one would see through my blindness. I'll give it a shot
              and shout back if it fails (which I doubt). Morning brain fart on my part.
              Glad you are in the office early today.

              John Warner




              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Tim Mitchell
              > Sent: Thursday, May 01, 2008 9:30 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: RE: [SQLQueriesNoCode] Syntax error nested CASE
              >
              >
              > John, from what I can tell you don't need to nest a case
              > statement here
              > - just make it a single multipart case statement. Use something like:
              >
              >
              >
              > SELECT SUM(CASE WHEN agi.glacctcode = '000-1210-00' THEN 0
              >
              > WHEN agi.drcr = 'CR' THEN -agi.glamount
              >
              > ELSE agi.glamount END)
              >
              > AS Amount
              >
              >
              >
              > Hope this helps...
              >
              > Tim
              >
              >
              >
              > ________________________________
              >
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              > Sent: Thursday, May 01, 2008 8:22 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Syntax error nested CASE
              > Importance: Low
              >
              >
              >
              > I'm getting an error " Incorrect syntax near ')' "
              >
              > This is the code:
              >
              > sum(case when agi.glacctcode = '000-1210-00' then 0 else
              > case when agi.drcr = 'CR' then -agi.glamount else
              > agi.glamount end) AS Amount
              >
              > Clearly I'm doing something wrong, but the things I've tried
              > are not resolving it. What is the proper way to nest a CASE?
              >
              > In case it isn't clear what I'm after is if the account code
              > is '000-1210-00' then set the Amount field for this row = 0
              > else test drcr, if 'CR' then negate amount else leave it
              > alone for the sum function.
              >
              > Thanks
              >
              > (SQL Server 2K)
              >
              > John Warner
            • Paul Anca
              Hi John, Actually you start 2 CASE function and close just one with END, add another END :) it should fix your original statement. But the below solution to
              Message 6 of 22 , May 1, 2008
              • 0 Attachment
                Hi John,

                Actually you start 2 CASE function and close just one with END, add another END :) it should fix your original statement.

                But the below solution to use just one CASE is much better.

                Best regards,
                Paul Anca

                www.ancasolutions.com

                ----
                "If you were plowing a field, which would you rather use? Two strong oxen or 1024 chickens?"
                - Seymour Cray; father of supercomputing


                >John, from what I can tell you don't need to nest a case statement here
                >- just make it a single multipart case statement. Use something like:
                >
                >
                >
                >SELECT SUM(CASE WHEN agi.glacctcode = '000-1210-00' THEN 0
                >
                >WHEN agi.drcr = 'CR' THEN -agi.glamount
                >
                >ELSE agi.glamount END)
                >
                >AS Amount
                >
                >
                >
                >Hope this helps...
                >
                >Tim
                >
                >
                >
                >________________________________
                >
                >From: SQLQueriesNoCode@yahoogroups.com
                >[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                >Sent: Thursday, May 01, 2008 8:22 AM
                >To: SQLQueriesNoCode@yahoogroups.com
                >Subject: [SQLQueriesNoCode] Syntax error nested CASE
                >Importance: Low
                >
                >
                >
                >I'm getting an error " Incorrect syntax near ')' "
                >
                >This is the code:
                >
                >sum(case when agi.glacctcode = '000-1210-00' then 0 else
                >case when agi.drcr = 'CR' then -agi.glamount else agi.glamount end) AS
                >Amount
                >
                >Clearly I'm doing something wrong, but the things I've tried are not
                >resolving it. What is the proper way to nest a CASE?
                >
                >In case it isn't clear what I'm after is if the account code is
                >'000-1210-00' then set the Amount field for this row = 0 else test drcr,
                >if
                >'CR' then negate amount else leave it alone for the sum function.
                >
                >Thanks
                >
                >(SQL Server 2K)
                >
                >John Warner
                >
                >
                >
                >
                >
                >[Non-text portions of this message have been removed]
                >
                >
                >------------------------------------
                >
                >Yahoo! Groups Links
                >
                >
                >
              • John Warner
                Thanks Paul it s the obvious stuff that gets you. Alas I ve already had my morning coffee so that excuse no longer works. John Warner
                Message 7 of 22 , May 1, 2008
                • 0 Attachment
                  Thanks Paul it's the obvious stuff that gets you. Alas I've already had my
                  morning coffee so that excuse no longer works.

                  John Warner




                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Anca
                  > Sent: Thursday, May 01, 2008 10:27 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] Syntax error nested CASE
                  >
                  >
                  > Hi John,
                  >
                  > Actually you start 2 CASE function and close just one with
                  > END, add another END :) it should fix your original statement.
                  >
                  > But the below solution to use just one CASE is much better.
                  >
                  > Best regards,
                  > Paul Anca
                  >
                  > www.ancasolutions.com
                  >
                  > ----
                  > "If you were plowing a field, which would you rather use? Two
                  > strong oxen or 1024 chickens?"
                  > - Seymour Cray; father of supercomputing
                  >
                  >
                  > >John, from what I can tell you don't need to nest a case
                  > statement here
                  > >- just make it a single multipart case statement. Use
                  > something like:
                  > >
                  > >
                  > >
                  > >SELECT SUM(CASE WHEN agi.glacctcode = '000-1210-00' THEN 0
                  > >
                  > >WHEN agi.drcr = 'CR' THEN -agi.glamount
                  > >
                  > >ELSE agi.glamount END)
                  > >
                  > >AS Amount
                  > >
                  > >
                  > >
                  > >Hope this helps...
                  > >
                  > >Tim
                  > >
                  > >
                  > >
                  > >________________________________
                  > >
                  > >From: SQLQueriesNoCode@yahoogroups.com
                  > >[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  > >Sent: Thursday, May 01, 2008 8:22 AM
                  > >To: SQLQueriesNoCode@yahoogroups.com
                  > >Subject: [SQLQueriesNoCode] Syntax error nested CASE
                  > >Importance: Low
                  > >
                  > >
                  > >
                  > >I'm getting an error " Incorrect syntax near ')' "
                  > >
                  > >This is the code:
                  > >
                  > >sum(case when agi.glacctcode = '000-1210-00' then 0 else
                  > >case when agi.drcr = 'CR' then -agi.glamount else
                  > agi.glamount end) AS
                  > >Amount
                  > >
                  > >Clearly I'm doing something wrong, but the things I've tried are not
                  > >resolving it. What is the proper way to nest a CASE?
                  > >
                  > >In case it isn't clear what I'm after is if the account code is
                  > >'000-1210-00' then set the Amount field for this row = 0 else test
                  > >drcr, if 'CR' then negate amount else leave it alone for the sum
                  > >function.
                  > >
                  > >Thanks
                  > >
                  > >(SQL Server 2K)
                  > >
                  > >John Warner
                  > >
                  > >
                  > >
                  > >
                  > >
                  > >[Non-text portions of this message have been removed]
                  > >
                  > >
                  > >------------------------------------
                  > >
                  > >Yahoo! Groups Links
                  > >
                  > >
                  > >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • Paul Anca
                  Yes, this method will take more time, it will execute basically the same query you said is working with one parameter 2 times, but one query who execute longer
                  Message 8 of 22 , May 1, 2008
                  • 0 Attachment
                    Yes, this method will take more time, it will execute basically the same query you said is working with one parameter 2 times, but one query who execute longer is preferable to one which hung up.

                    I'm glad you find out a better solution.

                    Best regards,
                    Paul Anca

                    www.ancasolutions.com


                    ----
                    "If you were plowing a field, which would you rather use? Two strong oxen or 1024 chickens?"
                    - Seymour Cray; father of supercomputing


                    >This would I suspect slow things even more. But an interesting thought,
                    >thank you.
                    >
                    >John Warner
                    >
                    >
                    >
                    >
                    >> -----Original Message-----
                    >> From: SQLQueriesNoCode@yahoogroups.com
                    >> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Anca
                    >> Sent: Tuesday, April 29, 2008 11:58 AM
                    >> To: SQLQueriesNoCode@yahoogroups.com
                    >> Subject: RE: [SQLQueriesNoCode] Question about OR or IN
                    >>
                    >>
                    >> Hi,
                    >>
                    >> Just a thought: can you use a temp table?
                    >>
                    >> Something like this:
                    >>
                    >> SELECT [fields]
                    >> INTO #MyTemp
                    >> FROM [Original]
                    >> WHERE ...
                    >> AND agi.trncode = 'Sales Amount'
                    >>
                    >> INSERT INTO #MyTemp
                    >> SELECT [fields]
                    >> FROM [Original]
                    >> WHERE ...
                    >> AND agi.trncode = 'Financial Credit'
                    >>
                    >> SELECT DISTINCT * FROM #MyTemp
                    >>
                    >> DROP TABLE #MyTemp
                    >>
                    >> Best regards,
                    >> Paul Anca
                    >>
                    >> www.ancasolutions.com
                    >>
                    >>
                    >> ----
                    >> "If you were plowing a field, which would you rather use? Two
                    >> strong oxen or 1024 chickens?"
                    >> - Seymour Cray; father of supercomputing
                    >>
                    >>
                    >>
                    >> >SQL Server 2K on reasonably beefy hardware
                    >> >
                    >> >I have a rather lengthy and elaborate query in a stored proc
                    >> that runs
                    >> >in a reasonable amount of time (couple of minutes or so)
                    >> >
                    >> >If I make a change in one of the Sub Queries the thing times
                    >> out in a
                    >> >VBA app that actually calls the proc. Within the ADO I am set for 11
                    >> >minutes on the time out. Ok so the change:
                    >> >
                    >> >In the original I have this in a WHERE clause
                    >> >
                    >> >AND agi.trncode = 'Sales Amount'
                    >> >
                    >> >Again with just this simple test on this field all is well
                    >> but if I add
                    >> >this
                    >> >
                    >> >OR agi.trncode = 'Financial Credit'
                    >> >
                    >> >the query goes away. The OR here is not doubling the amount of rows
                    >> >that are later compared to other things etc.
                    >> >
                    >> >I have tried using IN to the same effect, goes away. Any
                    >> ideas on how
                    >> >to include rows that match the OR condition and not take a huge
                    >> >performance hit? I can post the entire query, but most of it
                    >> will not
                    >> >make any sense as the database is dozens of tables etc. There is no
                    >> >secret here so if someone thinks it will help trouble shoot
                    >> just shout.
                    >> >As to giving you DDL, that is not a practical solution here.
                    >> >
                    >> >Thanks
                    >> >
                    >> >John Warner
                    >> >
                    >> >
                    >> >
                    >> >------------------------------------
                    >> >
                    >> >Yahoo! Groups Links
                    >> >
                    >> >
                    >> >
                    >>
                    >> ------------------------------------
                    >>
                    >> Yahoo! Groups Links
                    >>
                    >>
                    >>
                    >
                    >
                    >
                    >------------------------------------
                    >
                    >Yahoo! Groups Links
                    >
                    >
                    >
                  • John Warner
                    Yes, found a solution, had to go with IN and a few tweaks to the way a where clause in the inner query worked. Thank you John Warner ... simply
                    Message 9 of 22 , May 2, 2008
                    • 0 Attachment
                      Yes, found a solution, had to go with IN and a few tweaks to the way a
                      where clause in the inner query worked. Thank you

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jigglypsyduck
                      > Sent: Thursday, May 01, 2008 12:07 AM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: [SQLQueriesNoCode] Re: Question about OR or IN
                      >
                      > Hi John,
                      >
                      > Were you able to resolve your problem?
                      >
                      > For your second question, why not do a left outer join from Sales to
                      > Cost?
                      >
                      >
                      > Could you send a copy of the from and where clause?
                      >
                      > Thanks,
                      > An
                      >
                      >
                      > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                      > <john@...> wrote:
                      > >
                      > > Yes, I found one of the other parts of my where was not being applied
                      > until
                      > > after the OR or IN, a bit of re-arranging helped solve that.
                      > >
                      > > Another question, guess I ought to change the header, but I'm lazy
                      > today.
                      > >
                      > > Same query, it UNION ALL to sub queries regarding Sales and Costs.
                      > I have
                      > > found that there are a couple of rows (among hundreds) of sales that
                      > don't
                      > > have matching costs. What I mean is to the Cost sub query this
                      > transactions
                      > > literally don't exist, not just this field or that is NULL, the row
                      simply
                      > > does not exist. But on the Sales side a row does exist. Is there
                      > anyway to
                      > > get a row somehow created for the cost side. My Sales row seems to
                      > be
                      > > dropping out due to the lack of a match when the UNION occurs? Any
                      > > suggestions for a solution?
                      > >
                      > > John Warner
                      > >
                      > >
                      > >
                      > >
                      > > > -----Original Message-----
                      > > > From: SQLQueriesNoCode@yahoogroups.com
                      > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                      > Michael Weiss
                      > > > Sent: Tuesday, April 29, 2008 11:43 AM
                      > > > To: SQLQueriesNoCode@yahoogroups.com
                      > > > Subject: RE: [SQLQueriesNoCode] Question about OR or IN
                      > > >
                      > > >
                      > > > Are you able to view the query plan, John?
                      > > >
                      > > >
                      > > >
                      > > > _____
                      > > >
                      > > > From: SQLQueriesNoCode@yahoogroups.com
                      > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John
                      > Warner
                      > > > Sent: Tuesday, April 29, 2008 5:55 AM
                      > > > To: SQLQueriesNoCode@yahoogroups.com
                      > > > Subject: RE: [SQLQueriesNoCode] Question about OR or IN
                      > > > Importance: Low
                      > > >
                      > > >
                      > > >
                      > > > ...
                      > > > "I have tried using IN to the same effect, goes away."
                      > > >
                      > > > Thanks for the reply though
                      > > >
                      > > > John Warner
                      > > >
                      > > > > -----Original Message-----
                      > > > > From: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%
                      > 40yahoogroups.com>
                      > > > yahoogroups.com
                      > > > > [mailto:SQLQueriesNoCode@
                      > > > <mailto:SQLQueriesNoCode%40yahoogroups.com>
                      > > > yahoogroups.com] On Behalf Of A.J. Morales
                      > > > > Sent: Tuesday, April 29, 2008 8:36 AM
                      > > > > To: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%
                      > 40yahoogroups.com>
                      > > > yahoogroups.com
                      > > > > Subject: Re: [SQLQueriesNoCode] Question about OR or IN
                      > > > >
                      > > > >
                      > > > > how about:
                      > > > >
                      > > > > AND (agi.trncode in ('Sales Amount','Financial Credit'))
                      > > > >
                      > > > > AJ
                      > > > >
                      > > > > SQL Server 2K on reasonably beefy hardware
                      > > > >
                      > > > > I have a rather lengthy and elaborate query in a stored proc
                      > > > > that runs in a reasonable amount of time (couple of minutes or so)
                      > > > >
                      > > > > If I make a change in one of the Sub Queries the thing times
                      > > > > out in a VBA app that actually calls the proc. Within the ADO
                      > > > > I am set for 11 minutes on the time out. Ok so the change:
                      > > > >
                      > > > > In the original I have this in a WHERE clause
                      > > > >
                      > > > > AND agi.trncode = 'Sales Amount'
                      > > > >
                      > > > > Again with just this simple test on this field all is well
                      > > > > but if I add this
                      > > > >
                      > > > > OR agi.trncode = 'Financial Credit'
                      > > > >
                      > > > > the query goes away. The OR here is not doubling the amount
                      > > > > of rows that are later compared to other things etc.
                      > > > >
                      > > > > I have tried using IN to the same effect, goes away. Any
                      > > > > ideas on how to include rows that match the OR condition and
                      > > > > not take a huge performance hit? I can post the entire query,
                      > > > > but most of it will not make any sense as the database is
                      > > > > dozens of tables etc. There is no secret here so if someone
                      > > > > thinks it will help trouble shoot just shout. As to giving
                      > > > > you DDL, that is not a practical solution here.
                      > > > >
                      > > > > Thanks
                      > > > >
                      > > > > John Warner
                      > > > >
                      > > > >
                      > > > >
                      > > > >
                      > __________________________________________________________
                      > > > > ______________________
                      > > > > Be a better friend, newshound, and
                      > > > > know-it-all with Yahoo! Mobile. Try it now.
                      > > > > http://mobile.
                      > > > <http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ>
                      > > > yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
                      > > > >
                      > > > > ------------------------------------
                      > > > >
                      > > > > Yahoo! Groups Links
                      > > > >
                      > > > >
                      > > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > > __________ Information from ESET Smart Security, version of
                      > > > virus signature database 3062 (20080429) __________
                      > > >
                      > > >
                      > > > The message was checked by ESET Smart Security.
                      > > >
                      > > >
                      > > > http://www.eset.com
                      > > >
                      > > >
                      > > >
                      > > > __________ Information from ESET Smart Security, version of
                      > > > virus signature database 3063 (20080429) __________
                      > > >
                      > > > The message was checked by ESET Smart Security.
                      > > >
                      > > > http://www.eset.com
                      > > >
                      > > >
                      > > >
                      > > > [Non-text portions of this message have been removed]
                      > > >
                      > > >
                      > > > ------------------------------------
                      > > >
                      > > > Yahoo! Groups Links
                      > > >
                      > > >
                      > > >
                      > >
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    Your message has been successfully submitted and would be delivered to recipients shortly.