Re: Pulling data that could be in one of 2 tables

I couldnt figure out how to get your query suggestion to work with this but
after reading your blog I figured out my problem. I was comaparing my results
in my Case statement with Null. So instead of using '=' I used EXISTS and
that did the trick. My resulting query came out to be:

SELECT SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.ORIGNUMB,
SOP30200.DOCID, SOP30200.DOCDATE, SOP30200.INVODATE,
SOP30200.MSTRNUMB, SOP30200.DOCAMNT, CASE WHEN EXISTS
(SELECT DOCID
FROM SOP10100
WHERE SOP10100.SOPNUMBE =
SOP30200.ORIGNUMB) THEN SOP10100.DOCID ELSE
(SELECT DOCID
FROM SOP30200 AS History
WHERE History.SOPNUMBE = SOP30200.ORIGNUMB
AND History.SOPTYPE = '2') END AS ORIGDOCID
FROM SOP30200 LEFT OUTER JOIN
SOP10100 ON SOP30200.ORIGNUMB = SOP10100.SOPNUMBE
WHERE (SOP30200.SOPTYPE = '3') AND (SOP30200.DOCDATE >= @BeginDate) AND
(SOP30200.DOCDATE <= @EndDate)

Probably not the most efficient way to do it but it got the job done and my
numbers came out exact.

Thanks to you and Russel for your input.

"Hugo Kornelis" wrote:

On Thu, 25 Oct 2007 07:30:01 -0700, Matt M wrote:

(snip)
Does this help? Your sample query you showed me looks promising but it may
take me a little bit to wrap my brain around it.

Hi Matt,

The main reason that this query is ntroublesome to come up with, is that
the design is bad. I'm not sure if you are able to change that (never
worked with GP, and after seeing their table and column names, I hope to
keep it that way). An order should be in the same table, whether it's
partially or fully shipped, or not shipped at all.

Anyway, if you are stuck with the design, than try sometinh like this:

SELECT base.SOPTYPE, base.SOPNUMBE, base.ORIGNUMB,
COALESCE(orig1.SOPTYPE, orig2.SOPTYPE),
COALESCE(orig1.SOPNUMBE, orig2.SOPNUMBE)
FROM table2 AS base
LEFT JOIN table1 AS orig1
ON orig1.SOPNUMBE = base.ORIGNUMBE
LEFT JOIN table2 AS orig2
ON orig2.SOPNUMBE = base.ORIGNUMBE
WHERE base.SOPTYPE = 3; -- Invoices only

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

.

Relevant Pages

  • Re: Error executing MDX query
    ... Dimension PROPERTIES.Name, ... Blog: http://www.geekswithblogs.net/darrengosbell ... An error occurred while preparing a query for execution against OLE DB ...
    (microsoft.public.sqlserver.olap)
  • RE: MDX Named Set and OPENQUERY
    ... I also agree with the suggestions at the end of your blog. ... Is restructuring the query to move the set definition inline in the ... string, [SelectedStores], was parsed.". ... I proceeded to copy the query from the above error message into an MDX ...
    (microsoft.public.sqlserver.olap)
  • Re: hiding contacts (ojbect) from directory search (LDAP)
    ... what you are saying is that a group of people are able to query ... # Jorge de Almeida Pinto # MVP Windows Server - Directory Services ... BLOG --> http://blogs.dirteam.com/blogs/jorge/default.aspx ... * This posting is provided "AS IS" with no warranties and confers no rights! ...
    (microsoft.public.windows.server.active_directory)
  • Re: Forcing Global Catalog Replication
    ... # Jorge de Almeida Pinto # MVP Windows Server - Directory Services ... BLOG --> http://blogs.dirteam.com/blogs/jorge/default.aspx ... the results of the query to change. ...
    (microsoft.public.windows.server.active_directory)
  • Re: ADmodcmd & Query
    ... you might also wanna have a look at ADFIND and ADMOD from joeware.net ... BLOG --> http://blogs.dirteam.com/blogs/jorge/default.aspx ... The "error"it gave was that the query did not match the filter. ... query on this specific test user is exactly the same. ...
    (microsoft.public.windows.server.active_directory)