Page 1 of 1
SQL Join problems
Posted: 01 Jun 2009, 12:21
by SBSP
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 ?
Re: Joint problems
Posted: 01 Jun 2009, 12:24
by Ron2K
I think an OUTER JOIN of one kind or another may be your friend here.
Re: Joint problems
Posted: 01 Jun 2009, 12:35
by c0d3r
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.
Re: Joint problems
Posted: 01 Jun 2009, 12:35
by Tribble
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?
Re: Joint problems
Posted: 01 Jun 2009, 12:37
by c0d3r
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
Posted: 01 Jun 2009, 13:10
by SBSP
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.
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
Re: SQL Join problems
Posted: 01 Jun 2009, 14:10
by RuadRauFlessa
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.
Re: SQL Join problems
Posted: 01 Jun 2009, 14:50
by SBSP
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.
I'm thinking of creating 3 views then a final view combined from that.
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.
Re: SQL Join problems
Posted: 01 Jun 2009, 15:12
by RuadRauFlessa
SBSP wrote: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.
I'm thinking of creating 3 views then a final view combined from that.
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.
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.
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.
Re: SQL Join problems
Posted: 01 Jun 2009, 15:15
by SBSP
Will a query lock a table if the logon user has read only rights ?
Re: SQL Join problems
Posted: 01 Jun 2009, 15:20
by Ron2K
For me to explain when each type of locking occurs is a separate essay in itself.
Re: SQL Join problems
Posted: 01 Jun 2009, 15:23
by RuadRauFlessa
SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
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.
Re: SQL Join problems
Posted: 01 Jun 2009, 15:30
by SBSP
RuadRauFlessa wrote:SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
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.
Thanks ron2k and RuadRauFlessa.
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.