March 8, 2007 at 7:37 am
I have the following query:
declare @saleid int
set @saleid = 67026
Set nocount on
Print 'Please Rollback:'
Print ''
Print 'System: Production'
select 'Booking:',cast(Sale.SaleID AS VARCHAR)+ ' - '
+ case
when TerminationInfo.TerminationTypeID
= 1000 then 'StandardTermination'
else ''
End
+ case
when Sale.NoticeReasonID = 9000
then ' - move to ' +
cast(salereverse.saleid as VARCHAR)
else ''
end
+ char(13) + char(10) +
'Centre: ' + Centre.CentreName
+ char(13) + char(10) +
'Company: ' + Company.CompanyName
+ char(13) + char(10) +
'Room(s): ' + Inventory.inventoryname
+ char(13) + char(10) +
+ char(13) + char(10) +
+ case
when Sale.NoticeReasonID = 9000
then '(Moved part ' +
cast(salereverse.saleid as VARCHAR) + ')'
else ''
end
from Sale
inner JOIN company
on Sale.CompanyID = Company.CompanyID and
Sale.companyversion = Company.companyversion
inner JOIN SaleStatus
on Sale.SaleStatusID = SaleStatus.SaleStatusID
inner JOIN TerminationInfo
on Sale.SaleID = TerminationInfo.SaleID
inner JOIN Centre
on Sale.centreID = Centre.CentreID and
Sale.CentreVersion = Centre.CentreVersion
inner JOIN inventorysale
on sale.saleid = inventorysale.saleid
inner JOIN InventoryUsage
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
inner JOIN office
on InventoryUsage.officeID = office.officeid
and InventoryUsage.officeVersion = office.officeVersion
inner JOIN inventory
on Inventoryusage.inventoryID = inventory.inventoryid
inner JOIN sale salereverse
on salereverse.relatedsaleid = @saleid
where sale.saleID = @saleid
Problem is that there will not always be a related record in some of the tables... eg. TerminationInfo, I think that this will make it not bring back any data. is this true? and if so, how do i get around this?
Dion
March 8, 2007 at 10:03 am
ok, ok, ok,
it was left joins, thus:
declare @saleid int
set @saleid = 71476
Set nocount on
Print 'Please Rollback:'
Print ''
Print 'System: Production'
select 'Booking:',cast(Sale.SaleID AS VARCHAR)
+ case
when TerminationInfo.TerminationTypeID
= 1000 then ' - StandardTermination'
else ' - unterminated'
End
+ case
when Sale.NoticeReasonID = 9000
then ' - move to ' +
cast(salereverse.saleid as VARCHAR)
else ''
end
+ char(13) + char(10) +
'Centre: ' + Centre.CentreName
+ char(13) + char(10) +
'Company: ' + Company.CompanyName
+ char(13) + char(10) +
'Room(s): ' + Inventory.inventoryname
+ char(13) + char(10) +
+ char(13) + char(10) +
+ case
when Sale.NoticeReasonID = 9000
then '(Moved part ' +
cast(salereverse.saleid as VARCHAR) + ')'
else ''
end
+ char(13) + char(10)
from Sale
inner JOIN company
on Sale.CompanyID = Company.CompanyID and
Sale.companyversion = Company.companyversion
left JOIN TerminationInfo
on Sale.SaleID = TerminationInfo.SaleID
inner JOIN Centre
on Sale.centreID = Centre.CentreID and
Sale.CentreVersion = Centre.CentreVersion
inner JOIN inventorysale
on sale.saleid = inventorysale.saleid
inner JOIN InventoryUsage
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
inner JOIN office
on InventoryUsage.officeID = office.officeid
and InventoryUsage.officeVersion = office.officeVersion
inner JOIN inventory
on Inventoryusage.inventoryID = inventory.inventoryid
left JOIN sale salereverse
on salereverse.relatedsaleid = @saleid
where sale.saleID = @saleid
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy