1. SELECT
  2. p.ProductCode AS id
  3. , p.ProductName AS [stripHTML-title]
  4. , IsNull(IsNull(p.Google_Product_Type, pp.Google_Product_Type),
  5. 'n/a') AS product_type
  6. , IsNull(IsNull(p.Google_Product_Category, pp.Google_Product_Category),
  7. 'n/a') AS google_product_category
  8. , IsNull(p.SalePrice,p.ProductPrice) AS price
  9. , IsNull(p.ProductManufacturer,'n/a') AS brand
  10. , IsNull(p.ProductCondition,'n/a') AS condition
  11. , CONVERT(VARCHAR(10), (GETDATE() + 29),120) AS expiration_date
  12. , p.ProductDescription AS [stripHTML-description]
  13. , 'Config_FullStoreURLConfig_ProductPhotosFolder/' + p.ProductCode
  14. + '-2T.jpg' AS image_link
  15. , 'Config_FullStoreURLProductDetails.asp?ProductCode='
  16. + p.ProductCode + '&click=2' AS link
  17. , CAST(IsNull(p.ProductWeight,pp.ProductWeight) as VARCHAR(30)) + ' lb' as shipping_weight
  18. , IsNull(p.Google_Age_Group, pp.Google_Age_Group) as age_group
  19. , IsNull(p.Google_Availability, pp.Google_Availability) as availability
  20. , IsNull(p.Google_Color, pp.Google_Color) as color
  21. , IsNull(p.Google_Gender, pp.Google_Gender) as gender
  22. , IsNull(p.Google_Material, pp.Google_Material) as material
  23. , IsNull(p.Google_Pattern, pp.Google_Pattern) as pattern
  24. , IsNull(p.Google_Size, pp.Google_Size) as size
  25. , IsNull(p.UPC_Code, pp.UPC_Code) as gtin
  26. , IsNull(p.Book_ISBN, pp.Book_ISBN), IsNull(p.EAN, pp.EAN) as mpn
  27. , p.IsChildOfProductCode as item_group_id
  28. FROM vMerchant.Products_Joined p
  29. LEFT OUTER JOIN vMerchant.Products_Joined pp
  30. ON p.IsChildOfProductCode = pp.ProductCode
  31. WHERE ISNULL(p.EnableMultiChildAddToCart, 'N') = 'N'
  32. AND ISNULL(p.EnableOptions_InventoryControl, 'N') = 'N'
  33. AND (ISNULL(p.HideProduct,'N') <> 'Y'
  34. OR ISNULL(p.IsChildOfProductCode,'') <> '')
  35. AND (ISNULL(p.Google_Product_Category, '') <>
  36. '' OR ISNULL(pp.Google_Product_Category, '') <> '')
  37. ORDER BY p.ProductCode