Skip to Content
0
Former Member
Dec 04, 2008 at 04:34 AM

Join problem in SQL query

21 Views

Hi,

I have the follwoing two tables and an alias table in the universe.

CustConv / SalesDetailed and Alias SalesDetailed_INV

The alias table is applied because I want to view SalesDetailed but only where SalesDetailed.Tran-Type = 'S' as a business rule in the universe (I don't want to query this in every report)

When I try to list all values from SalesDetailed_INV where CustConv."Channel" = 'NZ' and SalesDetailed is between two dates the query will just run forever with no result.

As soon as I take out either the dates between part of the where clause or the channel part of the where clause it works fine, takes 2 - 3 seconds to show results.

I am missing the point on why it is not working with both where clauses in the statement, here is the statement that the BO report built:

SELECT

RO.dbo.CustConv."Channel",

SalesDetailed_INV."Net Value"

FROM

RO.dbo.SalesDetailed SalesDetailed_INV INNER JOIN RO.dbo.SalesDetailed ON (SalesDetailed_INV.UniqueCount=RO.dbo.SalesDetailed.UniqueCount)

INNER JOIN RO.dbo.CustConv ON (RO.dbo.SalesDetailed."Customer #"=RO.dbo.CustConv."Customer No")

WHERE

( SalesDetailed_INV."Tran-Type"='S' )

AND

(

RO.dbo.CustConv."Channel" In ( 'NZ' )

AND

RO.dbo.SalesDetailed."Doc Date" BETWEEN '11/01/2008 00:0:0' AND '11/30/2008 00:0:0'

)

Any one with any suggestions please? I am assuming it has something to do with the join?