SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum field language translation craziness
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seandamnit
Starting Member

1 Posts

Posted - 03/13/2013 :  02:32:02  Show Profile  Reply with Quote
Hey kids,

I'm attempting to pull a data set in to excel that displays current active inventory in our system, by store and SKU. Getting the actual store's name is tricky...the system doesn't give you the store's name directly, instead it gives you a column called StoreNameID in the iQClerk_Stores table, which you need to join with ReferenceID from a table called LanguageTranslations, and use the FieldText column from that table to get the actual store name. Complicated right? It looks something like this:


SELECT LanguageTranslations.FieldText, 
  iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier, 
  iQclerk_StoresAndNonSellableProducts.Quantity

FROM ArchTelecom.dbo.iQclerk_GlobalProducts iQclerk_GlobalProducts, 
  ArchTelecom.dbo.iQclerk_Stores iQclerk_Stores, 
  ArchTelecom.dbo.iQclerk_StoresAndNonSellableProducts iQclerk_StoresAndNonSellableProducts, 
  ArchTelecom.dbo.LanguageTranslations LanguageTranslations

WHERE iQclerk_StoresAndNonSellableProducts.StoreID = iQclerk_Stores.StoreID AND 
  iQclerk_Stores.StoreNameID = LanguageTranslations.ReferenceID AND 
  iQclerk_GlobalProducts.GlobalProductID = iQclerk_StoresAndNonSellableProducts.GlobalProductID AND 
  ((iQclerk_Stores.Enabled=1))


And the expected result looks like this:


Quantity FieldText StoreID ProductIdentifier
1        GrandAve  37      ACCCCCC1101
1        GrandAve  37      ACCCCCC1101
1        Dover     52      ACCCCCC1105
1        Main      37      ACCCCCC1106
.
.
.


Anyway, I'd like to SUM up the Quantity column, but that weird FieldText field doesn't play nice with GROUP BY it seems. So when I change the code to look like this:


SELECT iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier,
  LanguageTranslations.FieldText
  Sum(iQclerk_StoresAndNonSellableProducts.Quantity)

FROM ArchTelecom.dbo.iQclerk_GlobalProducts iQclerk_GlobalProducts, 
  ArchTelecom.dbo.iQclerk_Stores iQclerk_Stores, 
  ArchTelecom.dbo.iQclerk_StoresAndNonSellableProducts iQclerk_StoresAndNonSellableProducts, 
  ArchTelecom.dbo.LanguageTranslations LanguageTranslations

WHERE iQclerk_StoresAndNonSellableProducts.StoreID = iQclerk_Stores.StoreID AND 
  iQclerk_Stores.StoreNameID = LanguageTranslations.ReferenceID AND 
  iQclerk_GlobalProducts.GlobalProductID = iQclerk_StoresAndNonSellableProducts.GlobalProductID AND 
  ((iQclerk_Stores.Enabled=1))

GROUP BY iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier


I get this error:

https://www.dropbox.com/s/ripkhnlh1yuz9sc/field%20text.png

Hope this is enough info. Any help is much appreciated!

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/13/2013 :  02:52:42  Show Profile  Reply with Quote
GROUP BY iQclerk_Stores.StoreID,
iQclerk_GlobalProducts.ProductIdentifier,
LanguageTranslations.FieldText


If you want sum of quantity based on StoreId and ProductIdentifier only.... then use Over() clause with SUM aggregate

Better to post error message( by copy and paste).. some companies might not allow external links.....

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000