Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.
COURSE of the MONTH
8 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to get the join to work ?
Posted on 2013-01-04
Hi!
Have this sql-code:
When i run this query, it takes about 25 minits to complete ?
[hgf-sql01].[hfgh-axdb5-dr
-> contains of 1039137 records
[hgf-sql01].[hfgh-axdb5-dr
-> contains of 18045 records
But the strange ting is that query
always return more then 1 million records ???
Tryed to change this lines
TO:
OR:
But dosent help....
I acnt return more records then the table:
[hgf-sql01].[hfgh-axdb5-dr
What is wrong here ?
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-dr
-> contains of 1039137 records
[hgf-sql01].[hfgh-axdb5-dr
-> 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-dr
What is wrong here ?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
It is very hard to tell without the execution plan. Can you please let me know the index details for the tables SALESLINE and CUSTTABLE?
Is CUSTACCOUNT, ACCOUNTNUM and DATAAREAID are indexed columns?
Is CUSTACCOUNT, ACCOUNTNUM and DATAAREAID are indexed columns?
Active 6 days ago
It means that your INNER JOINs are yielding duplicates. Maybe you do not have the correct fields involved in the ON clause. In other words, check if multiple columns combine to form the unique identifier for the matches.
Note: if you use a LEFT JOIN from the SALESLINE table, you will get 1,039,137 (i.e., over a million) rows. Therefore, if your intent is to get all the CUSTTABLE records and only their matches, then use INNER JOIN or make CUSTTABLE the left-most table in the LEFT JOIN. If you get more rows than expected, then resolve the duplicates.
As a final thought, the issue may be that the duplicates are appropriate. A customer should have multiple matches in a sales order table, especially sales order lines—one sales order can have multiple items or delivery lines. Therefore, what you may need is a derived query to aggregate SALESLINE data by customer.
For example:
You can make this by customer, by year, by item. Just note that the number of rows will be more than what is in the customer table by the amount of variability in sales. The more items per customer, the more years per customer, et cetera, the more lines per customer in the final SELECT.
I hope that makes sense.
Best regards and happy coding,
Kevin
Note: if you use a LEFT JOIN from the SALESLINE table, you will get 1,039,137 (i.e., over a million) rows. Therefore, if your intent is to get all the CUSTTABLE records and only their matches, then use INNER JOIN or make CUSTTABLE the left-most table in the LEFT JOIN. If you get more rows than expected, then resolve the duplicates.
As a final thought, the issue may be that the duplicates are appropriate. A customer should have multiple matches in a sales order table, especially sales order lines—one sales order can have multiple items or delivery lines. Therefore, what you may need is a derived query to aggregate SALESLINE data by customer.
For example:
FROM CUSTTABLE CUST
/* INNER JOIN to return only customers with sales; OUTER JOIN for all customers. */
LEFT JOIN (
SELECT CUSTACCOUNT, sum(SALE.QTYORDERED)
FROM SALESLINE
GROUP BY CUSTACCOUNT
) SALE(CUSTACCOUNT, QTYORDERED) ON SALE.CUSTACCOUNT = CUST.ACCOUNTNUM
You can make this by customer, by year, by item. Just note that the number of rows will be more than what is in the customer table by the amount of variability in sales. The more items per customer, the more years per customer, et cetera, the more lines per customer in the final SELECT.
I hope that makes sense.
Best regards and happy coding,
Kevin
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month8 days, 12 hours left to enroll
764 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.