troubleshooting Question

How to get the join to work ?

Avatar of team2005
team2005 asked on
Microsoft SQL Server
3 Comments1 Solution367 ViewsLast Modified:
Hi!

Have this sql-code:

      SELECT distinct
           CUST.ABC_FORECASTGROUPID,
           FORCAST.DESCRIPTION,
           SALE.ITEMID,
           sum(SALE.QTYORDERED),
           DATACELLS.cellvalue,
           cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2))
        FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST
        ON
            (
                 ((SALE.CUSTACCOUNT = CUST.ACCOUNTNUM) AND
                 (SALE.DATAAREAID  COLLATE DATABASE_DEFAULT = CUST.DATAAREAID  COLLATE DATABASE_DEFAULT))
            )
        INNER JOIN
            [hgf-sql01].[bizview_hfs].[dat].[data_cells] AS DATACELLS
        ON
            (
                ((CUST.ABC_FORECASTGROUPID COLLATE DATABASE_DEFAULT = DATACELLS.ws_name COLLATE DATABASE_DEFAULT) OR
                (CUST.ABC_FORECASTGROUPID=' ')) AND (DATACELLS.version_id='PU4012') AND (DATACELLS.valtype_id='ANT') AND
                (DATACELLS.timeunit_id='ISOWEEK') AND (DATACELLS.timeval_id=cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2)))
            )
            
        INNER JOIN
            [hgf-sql01].[bizview_hfs].[dat].[data_dimensions] AS DIMENTIONS
        ON
            (
                ((DATACELLS.dim_cluster_id  = DIMENTIONS.dim_cluster_id) AND
                (DIMENTIONS.obj_id='item') AND (DATACELLS.client_id  = DIMENTIONS.client_id))
                  
                   
            ) 
            
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[ABC_FORECASTGROUP] AS FORCAST
        ON
            (
                 ((FORCAST.FORECASTGROUPID = CUST.ABC_FORECASTGROUPID) AND
                 (FORCAST.DATAAREAID = CUST.DATAAREAID))
                  
                   
            ) 
 
group by
    CUST.ABC_FORECASTGROUPID,
    FORCAST.DESCRIPTION,
    SALE.ITEMID,
    DATACELLS.cellvalue,
    cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2))

When i run this query, it takes about 25 minits to complete ?

 [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
-> contains of 1039137 records

   [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST
-> contains of 18045 records

But the strange ting is that query
always return more then 1 million records ???

Tryed to change this lines

       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST

TO:
       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
       LEFT JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST

OR:
       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
       right JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST


But dosent help....

I acnt return more records then the table:
 [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE]


What is wrong here ?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros