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
 Amending SQL but getting error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raindear
Yak Posting Veteran

64 Posts

Posted - 11/20/2013 :  09:57:14  Show Profile  Reply with Quote
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.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>

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/20/2013 :  11:24:36  Show Profile  Visit webfred's Homepage  Reply with Quote
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>





Too old to Rock'n'Roll too young to die.
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 11/20/2013 :  11:34:13  Show Profile  Reply with Quote
Still getting the same error but thanks for the tip. Will see if I can get it to work
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/20/2013 :  11:36:38  Show Profile  Visit webfred's Homepage  Reply with Quote
have you seen the part:
from dbo.productsection pc
join pc.ProductID = p.ProductID and pc.SectionID = 5345



Too old to Rock'n'Roll too young to die.
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 11/20/2013 :  11:50:01  Show Profile  Reply with Quote
Hi Yes I have removed that so that its referenced just the one time.
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 11/20/2013 :  12:02:46  Show Profile  Reply with Quote
Got it working! THanks :)
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.08 seconds. Powered By: Snitz Forums 2000