SQL Join problems
SQL Join problems
Hi I'm building a report that shows the following
Item , Item Desc , Qty Sold , QTY On hand, QTY Backorder.
The report run's year to May 2009 based on the sale date.
Ive create a seperate Sales report , Stock Onhand & Backorder report.
I then imported the 3 reports into an Access database then use crystal reports to
report from it.
All the reports are now a table.
Sales Table
Item, Qty Sold
Qty On hand
Item, QtyOnhand
Backorders Table
Item, QtyBackorder
And im linking the 3 together on the Item (Which is unique)
Sales Item is linked to Qty Onhand and Backorders is also linked to the Sales tables Item Field.
The Problem is.
Some items in the Sales table is not pressent cus its based on date, which is pressent on Bac orders and Same with QtyOnhand some items is not pressent on sales where there is stock available , hope it makes sense.
So when these are linked the Join matches only what it can find, render ing my report useless.
I can set the type of joining in Crystal Reports but ive tried all the combinations and it still doesnt work.
Can any one help me ?
Item , Item Desc , Qty Sold , QTY On hand, QTY Backorder.
The report run's year to May 2009 based on the sale date.
Ive create a seperate Sales report , Stock Onhand & Backorder report.
I then imported the 3 reports into an Access database then use crystal reports to
report from it.
All the reports are now a table.
Sales Table
Item, Qty Sold
Qty On hand
Item, QtyOnhand
Backorders Table
Item, QtyBackorder
And im linking the 3 together on the Item (Which is unique)
Sales Item is linked to Qty Onhand and Backorders is also linked to the Sales tables Item Field.
The Problem is.
Some items in the Sales table is not pressent cus its based on date, which is pressent on Bac orders and Same with QtyOnhand some items is not pressent on sales where there is stock available , hope it makes sense.
So when these are linked the Join matches only what it can find, render ing my report useless.
I can set the type of joining in Crystal Reports but ive tried all the combinations and it still doesnt work.
Can any one help me ?
Last edited by SBSP on 01 Jun 2009, 13:02, edited 1 time in total.
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: Joint problems
I think an OUTER JOIN of one kind or another may be your friend here.
Kia kaha, Kia māia, Kia manawanui.
Re: Joint problems
I seriously thought that this is going to be a thread about sore knees, elbows and hips when I read "Joint Problems".
*sigh*
Yes, an inner join only takes records where it finds a match in both tables. Interesting read.
*sigh*
Yes, an inner join only takes records where it finds a match in both tables. Interesting read.
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Joint problems
Not used Crystal but can help you in Access if you want. Ronny is right - you need to set your outer join to be everything in Sales table visible. You never mentioned any dates in your fields though.
Which table do you want to see all the records of?
Which table do you want to see all the records of?
Re: Joint problems
Rather keep your calculations and stuff to a minimum in Crystal. Do Group By's and Order By's and totalling etc in Crystal. Crystal gets clumsy with "raw" data and what ever you want to do, it's so much more easier and versatile in SQL. Except off course displaying the actual report
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
Re: SQL Join problems
I changed the topic to SQL Join Problems
And i made quickly made this macro.
Dont laugh please.
I have 4 sheets SALES, BACKORDERS and STOCKONHAND & COMBINED.
The Stock on hand is all items including 0 and old items which comes to 16 000 Items
So my macro runs in loops with in loops which finds items and puts the value into the correct field. in 3 stages.
It works but takes long, atleast i can give a report for now.
I will try the outer join thing for a permanent solution. I some how think that it wont work cut i think i have tried it.
But the stock on hand inventory list wasnt showing 0 on hand items though.
And i made quickly made this macro.
Dont laugh please.
I have 4 sheets SALES, BACKORDERS and STOCKONHAND & COMBINED.
The Stock on hand is all items including 0 and old items which comes to 16 000 Items
So my macro runs in loops with in loops which finds items and puts the value into the correct field. in 3 stages.
It works but takes long, atleast i can give a report for now.
I will try the outer join thing for a permanent solution. I some how think that it wont work cut i think i have tried it.
But the stock on hand inventory list wasnt showing 0 on hand items though.
Code: Select all
Dim QTYOnhand As Long
Dim QtySold As Long
Dim QtyBack As Long
Dim ItemNoSold As String
Dim ItemNoBack As String
Dim ItemNoOnhand As String
Dim LineCountOnhand As Double
Dim LineCountBack As Long
Dim LineCountSold As Long
Dim ItemDesc As String
Dim CombLinecount As Double
Sub Main()
CombLinecount = 0
For LineCountOnhand = 1 To 16688
CombLinecount = CombLinecount + 1
ItemNoOnhand = Sheet3.Cells(LineCountOnhand, 1)
ItemDesc = Sheet3.Cells(LineCountOnhand, 2)
QTYOnhand = Sheet3.Cells(LineCountOnhand, 3)
Sheet4.Cells(LineCountOnhand + 1, 1) = ItemNoOnhand
Sheet4.Cells(LineCountOnhand + 1, 2) = ItemDesc
Sheet4.Cells(LineCountOnhand + 1, 5) = QTYOnhand
Next LineCountOnhand
MsgBox "Complete with Onhand"
'Calculate Backorders
For LineCountBack = 1 To 810
ItemNoBack = Sheet2.Cells(LineCountBack, 1)
QtyBack = Sheet2.Cells(LineCountBack, 2)
For LineCountOnhand = 2 To 16688
ItemNoOnhand = Sheet4.Cells(LineCountOnhand, 1)
If ItemNoBack = ItemNoOnhand Then
Sheet4.Cells(LineCountOnhand, 4) = QtyBack
Exit For
End If
Next LineCountOnhand
Next LineCountBack
MsgBox "Complete with backorders"
'Calculate Sales
For LineCountSold = 1 To 1607
ItemNoSold = Sheet1.Cells(LineCountSold, 1)
QtySold = Sheet1.Cells(LineCountSold, 3)
For LineCountOnhand = 2 To 16688
ItemNoOnhand = Sheet4.Cells(LineCountOnhand, 1)
If ItemNoOnhand = ItemNoSold Then
Sheet4.Cells(LineCountOnhand, 3) = QtySold
Exit For
End If
Next LineCountOnhand
Next LineCountSold
MsgBox "Complete with Sales"
End Sub
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Join problems
SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
- Spoiler (show)
Re: SQL Join problems
I'm thinking of creating 3 views then a final view combined from that.RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
Or like you say a storedproc.
But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)
So when there is upgrades all this has to be upgraded or accounted for.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Join problems
All the more reason to use a single stored proc for the job. It will cut down on execution time and soforth. Also as a rule try and use nolock if you only draw data for reports.SBSP wrote:I'm thinking of creating 3 views then a final view combined from that.RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
Or like you say a storedproc.
But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)
So when there is upgrades all this has to be upgraded or accounted for.
SELECT * FROM [dbo].[TABLE1] WITH (NOLOCK)
The query will read over records with read/write locks. It will also not mark any records as read locked. The chance of getting a deadlock is thus null and void. You should, on a fairly busy system, see a notable performance difference.
- Spoiler (show)
Re: SQL Join problems
Will a query lock a table if the logon user has read only rights ?
Last edited by SBSP on 01 Jun 2009, 15:26, edited 2 times in total.
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: SQL Join problems
For me to explain when each type of locking occurs is a separate essay in itself.
Kia kaha, Kia māia, Kia manawanui.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Join problems
Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
- Spoiler (show)
Re: SQL Join problems
Thanks ron2k and RuadRauFlessa.RuadRauFlessa wrote:Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
Thought as much.
On a different note.
I can hardly read my own posts
I sometimes only type can when i meant "can't" and often type "is" when i meant if.
And to mix up those to words makes things confusing.