quote:

---

Originally posted by raindear

Hi

This script below executes a Google orders feed

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[

select p.productid, p.name, isnull(pv.name, '') VariantName,
p.description, p.sename, p.ImageFileNameOverride, p.SKU,
isnull(p.FroogleDescription, '') ProductFroogleDescription,
p.SEKeywords,
m.Name brand, p.ManufacturerPartNumber,
pv.price, isnull(pv.saleprice, 0) saleprice,
isnull(pv.FroogleDescription, '') VariantFroogleDescription,
isnull(pv.description, '') VariantDescr,
pv.inventory,
ROW_NUMBER() OVER (ORDER BY p.productid) AS ROW
from dbo.product p
join dbo.productvariant pv on p.productid = pv.productid
join dbo.ProductManufacturer pm on pm.ProductID = p.ProductID
join dbo.Manufacturer m on m.ManufacturerID = pm.ManufacturerID
join dbo.ProductStore ps on ps.ProductID = p.ProductID and ps.StoreID = 3
left join
(select variantid, sum(quan) inventory
from dbo.inventory group by variantid) i on pv.variantid = i.variantid
where p.IsSystem=0
and p.deleted = 0
and p.published = 1
and p.ExcludeFromPriceFeeds = 0
and pv.isdefault = 1
and case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0)
else pv.inventory end >= @HideProductsWithLessThanThisInventoryLevel
]]>
</sql>
<queryparam paramname="@HideProductsWithLessThanThisInventoryLevel" paramtype="appconfig" requestparamname="HideProductsWithLessThanThisInventoryLevel" sqlDataType="int" defvalue="0" validationpattern="" />
</query>






<query name="Categories" rowElementName="Category">
<sql>
<![CDATA[
select pc1.productid, pc1.categoryid, c.Name
from dbo.category c
join
(select pc.productid, min(pc.categoryid) categoryid
from dbo.ProductCategory pc
group by pc.productid) as pc1
on pc1.categoryid = c.categoryid
join EntityStore es on es.EntityType = 'category' and es.StoreID = 3 and es.EntityID = c.CategoryID
order by pc1.productid
]]>
</sql>

I am tryin to add another clause so it only selects products with a certain department number so in the code below I have added this

from dbo.productsection pc
join pc.ProductID = p.ProductID and pc.SectionID = 5345

But when I run it I get this error

"Exception=Incorrect syntax near the keyword 'and'.<br/>"

Any ideas where I am going wrong. Here is my new code

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[

select p.productid, p.name, isnull(pv.name, '') VariantName,
p.description, p.sename, p.ImageFileNameOverride, p.SKU,
isnull(p.FroogleDescription, '') ProductFroogleDescription,
p.SEKeywords,
m.Name brand, p.ManufacturerPartNumber,
pv.price, isnull(pv.saleprice, 0) saleprice,
isnull(pv.FroogleDescription, '') VariantFroogleDescription,
isnull(pv.description, '') VariantDescr,
pv.inventory,
ROW_NUMBER() OVER (ORDER BY p.productid) AS ROW
from dbo.product p
join dbo.productsection pc on pc.ProductID = p.ProductID and pc.SectionID = 5345
join dbo.productvariant pv on p.productid = pv.productid
join dbo.ProductManufacturer pm on pm.ProductID = p.ProductID
join dbo.Manufacturer m on m.ManufacturerID = pm.ManufacturerID
join dbo.ProductStore ps on ps.ProductID = p.ProductID and ps.StoreID = 3
left join
(select variantid, sum(quan) inventory
from dbo.inventory group by variantid) i on pv.variantid = i.variantid
where p.IsSystem=0
and p.deleted = 0
and p.published = 1
and p.ExcludeFromPriceFeeds = 0
and pv.isdefault = 1
and p.
and case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0)
else pv.inventory end >= @HideProductsWithLessThanThisInventoryLevel
from dbo.productsection pc
join pc.ProductID = p.ProductID and pc.SectionID = 5345
]]>
</sql>
<queryparam paramname="@HideProductsWithLessThanThisInventoryLevel" paramtype="appconfig" requestparamname="HideProductsWithLessThanThisInventoryLevel" sqlDataType="int" defvalue="0" validationpattern="" />
</query>






<query name="Categories" rowElementName="Category">
<sql>
<![CDATA[
select pc1.productid, pc1.categoryid, c.Name
from dbo.category c
join
(select pc.productid, min(pc.categoryid) categoryid
from dbo.ProductCategory pc
group by pc.productid) as pc1
on pc1.categoryid = c.categoryid
join EntityStore es on es.EntityType = 'category' and es.StoreID = 3 and es.EntityID = c.CategoryID
order by pc1.productid
]]>
</sql>

---