Click here to Skip to main content
15,440,465 members
Home / Discussions / Database
   

Database

 
GeneralRe: update query not updating Pin
turnerdavid7-May-15 10:26
Memberturnerdavid7-May-15 10:26 
GeneralRe: update query not updating Pin
ZurdoDev7-May-15 10:35
professionalZurdoDev7-May-15 10:35 
QuestionSybase Access Violaion Error Pin
Ankit RS6-May-15 21:32
MemberAnkit RS6-May-15 21:32 
SuggestionRe: Sybase Access Violaion Error Pin
ZurdoDev7-May-15 4:08
professionalZurdoDev7-May-15 4:08 
GeneralRe: Sybase Access Violaion Error Pin
Sascha Lefèvre7-May-15 6:25
professionalSascha Lefèvre7-May-15 6:25 
QuestionRe: Sybase Access Violaion Error Pin
Ankit RS8-May-15 0:36
MemberAnkit RS8-May-15 0:36 
AnswerRe: Sybase Access Violaion Error Pin
Sascha Lefèvre8-May-15 10:02
professionalSascha Lefèvre8-May-15 10:02 
QuestionUnion on a join Pin
jkirkerx6-May-15 9:20
professionaljkirkerx6-May-15 9:20 
I got myself in a pickle here.
I started letting customers delete items in the store. But I copied the item into a separate table.
I should of added a column status instead.

But anyways, on my report for total items sold in the year, I did a join on the product table, to get the current cost, images, etc.
So now that some items have been deleted, the record is no longer in the product table, but in the recycle table.

I tried a union all, but it results in 2 records, in which the 2nd is null.
I tried just union as well, but it produces an reader error.
I'm not sure how to proceed on this, or what to words to use to do a search here.
I'm stumped!

[EDIT]
Maybe I should of done a if exist around the join, and just switch tables.
Dim query As String = _
"DECLARE @startDate AS DATE; " & _
"DECLARE @stopDate AS DATE; " & _
"SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0); " & _
"SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); "

query +=
"SELECT " & _
" coH.PartNumber, " & _
" coH.ManPartNumber, " & _
" SUM(coH.Qty) as TotalQty, " & _
" CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost, " & _
" CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice, " & _
" pI.Cost, " & _
" pI.Price, " & _
" pI.ShortDescription, " & _
" pI.LongDescription, " & _
" pI.PostageImage, " & _
" pI.Thumbnail, " & _
" pI.VendorID, " & _
" pI.VendorName, " & _
" pI.Weight, " & _
" pI.ShipHeight, " & _
" pI.ShipWidth, " & _
" pI.ShipDepth, " & _
" pI.LimitedItem, " & _
" vI.Address1, " & _
" vI.Address2, " & _
" vI.City, " & _
" vI.StateCode, " & _
" vI.CountryCode, " & _
" vI.ZipCode, " & _
" vI.ContactName, " & _
" vI.VendorPhone " & _
" FROM CompletedOrdersCartHistory as coH " & _
" LEFT JOIN PRODUCTINFO AS pI " & _
" ON coH.PartNumber = pI.PartNumber " & _
" LEFT JOIN VendorInfo AS vI " & _
" ON pI.VendorID = vI.VendorID " & _
" WHERE coh.OrderDate > @startDate " & _
" AND coh.OrderDate < @stopDate " & _
" AND coh.PartNumber = @PartNumber " & _
" GROUP BY " & _
" coH.PartNumber, " & _
" coH.ManPartNumber,  " & _
" pI.Cost,  " & _
" pI.Price, " & _
" pI.ShortDescription,  " & _
" pI.LongDescription,  " & _
" pI.PostageImage,  " & _
" pI.Thumbnail,  " & _
" pI.VendorID,  " & _
" pI.VendorName, " & _
" pI.Weight,  " & _
" pI.ShipHeight,  " & _
" pI.ShipWidth,  " & _
" pI.ShipDepth,  " & _
" pI.LimitedItem, " & _
" vi.Address1,  " & _
" vI.Address2,  " & _
" vI.City,  " & _
" vI.StateCode,  " & _
" vI.CountryCode,  " & _
" vI.ZipCode,  " & _
" vI.ContactName,  " & _
" vI.VendorPhone "

query +=
" UNION ALL "

query +=
"SELECT " & _
" coH.PartNumber, " & _
" coH.ManPartNumber, " & _
" SUM(coH.Qty) as TotalQty, " & _
" CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost, " & _
" CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice, " & _
" pIH.Cost, " & _
" pIH.Price, " & _
" pIH.ShortDescription, " & _
" pIH.LongDescription, " & _
" pIH.PostageImage, " & _
" pIH.Thumbnail, " & _
" pIH.VendorID, " & _
" pIH.VendorName, " & _
" pIH.Weight, " & _
" pIH.ShipHeight, " & _
" pIH.ShipWidth, " & _
" pIH.ShipDepth, " & _
" pIH.LimitedItem, " & _
" vI.Address1, " & _
" vI.Address2, " & _
" vI.City, " & _
" vI.StateCode, " & _
" vI.CountryCode, " & _
" vI.ZipCode, " & _
" vI.ContactName, " & _
" vI.VendorPhone " & _
" FROM CompletedOrdersCartHistory as coH " & _
" LEFT JOIN PRODUCTINFO_RECYCLEBIN AS pIH " & _
" ON coH.PartNumber = pIH.PartNumber " & _
" LEFT JOIN VendorInfo AS vI " & _
" ON pIH.VendorID = vI.VendorID " & _
" WHERE coh.OrderDate > @startDate " & _
" AND coh.OrderDate < @stopDate " & _
" AND coh.PartNumber = @PartNumber " & _
" GROUP BY " & _
" coH.PartNumber,  " & _
" coH.ManPartNumber,  " & _
" pIH.Cost,  " & _
" pIH.Price, " & _
" pIH.ShortDescription,  " & _
" pIH.LongDescription,  " & _
" pIH.PostageImage,  " & _
" pIH.Thumbnail,  " & _
" pIH.VendorID,  " & _
" pIH.VendorName, " & _
" pIH.Weight,  " & _
" pIH.ShipHeight,  " & _
" pIH.ShipWidth,  " & _
" pIH.ShipDepth,  " & _
" pIH.LimitedItem, " & _
" vi.Address1,  " & _
" vI.Address2,  " & _
" vI.City,  " & _
" vI.StateCode,  " & _
" vI.CountryCode,  " & _
" vI.ZipCode,  " & _
" vI.ContactName,  " & _
" vI.VendorPhone "

query +=
"ORDER BY coH.PartNumber "


modified 6-May-15 15:32pm.

AnswerRe: Union on a join Pin
Richard Deeming6-May-15 9:40
mveRichard Deeming6-May-15 9:40 
GeneralRe: Union on a join Pin
jkirkerx6-May-15 11:40
professionaljkirkerx6-May-15 11:40 
GeneralRe: Union on a join Pin
jkirkerx7-May-15 12:24
professionaljkirkerx7-May-15 12:24 
Questionpostgresql VACUUM Pin
V.5-May-15 23:55
professionalV.5-May-15 23:55 
JokeRe: postgresql VACUUM Pin
Tim Carmichael6-May-15 4:52
MemberTim Carmichael6-May-15 4:52 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 14:06
professionalMycroft Holmes6-May-15 14:06 
GeneralRe: postgresql VACUUM Pin
Tim Carmichael6-May-15 14:49
MemberTim Carmichael6-May-15 14:49 
GeneralRe: postgresql VACUUM Pin
V.6-May-15 20:41
professionalV.6-May-15 20:41 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 22:04
professionalMycroft Holmes6-May-15 22:04 
GeneralRe: postgresql VACUUM Pin
V.6-May-15 22:06
professionalV.6-May-15 22:06 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 22:16
professionalMycroft Holmes6-May-15 22:16 
GeneralRe: postgresql VACUUM Pin
Tim Carmichael7-May-15 0:01
MemberTim Carmichael7-May-15 0:01 
AnswerRe: postgresql VACUUM Pin
Tim Carmichael7-May-15 2:15
MemberTim Carmichael7-May-15 2:15 
GeneralRe: postgresql VACUUM Pin
V.7-May-15 3:41
professionalV.7-May-15 3:41 
QuestionSequence number in DB2 Pin
sudevsu5-May-15 9:57
Membersudevsu5-May-15 9:57 
QuestionTest sol Pin
Stephen Holdorf30-Apr-15 11:24
MemberStephen Holdorf30-Apr-15 11:24 
GeneralRe: Help with a SQL query Pin
PIEBALDconsult30-Apr-15 19:33
professionalPIEBALDconsult30-Apr-15 19:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.