0

I have a problem with a Flexible Query. This is my query:

Select
{pp.productCode} as 'Code',
{p.descriptionCics} as 'Desc CISC',
{bs.uid} as 'Store',
{evo.code} as 'Status', 
{p.department} as 'Department', 
{pca.name} as 'Category',
{p.grm} as 'GRM',
{p.buyerCode} as 'Code Buyer',
{p.buyerids} as 'Buyer', 
{ps.planogramCode} as 'Code Planogram',
{pca.categoryCode} as 'Category Planogram',
{s.puvmBlock} as 'Blocked',
(CASE WHEN ({p.productDetailTypeList} is not null )THEN 'YES' else 
'NO' END) as 'IMAGE'
from 
{ 
Product as p
JOIN PlanogramProducts as pp on {p.code} = {pp.productCode}
JOIN StockLevel as s on {pp.productCode} = {s.productCode}
JOIN EnumerationValue as evo on {p.status} = {evo.pk}
JOIN PlanogramCategory as pc on {pp.planogramCode} = 
 {pc.planogramCode}
JOIN PlamnogramCategoryAnag as pca on {pc.categoryCode}= 
{pca.categoryCode}
JOIN BaseStore as bs JOIN PlanogramStore as ps on {bs.storeRef} = 
{ps.storeRef} AND {bs.bramchOffice} = {ps.branchOffice}
}
WHERE 1=1

and this my error when I execute it:

enter image description here

Can someone help me? Thanks a lot.

mike
  • 25
  • 10
  • Please do not add code as an image, instead please add it (formatted correctly) into the question. – taylor.2317 Feb 06 '22 at 19:51
  • @taylor.2317 THanks. I correct now. – mike Feb 07 '22 at 08:14
  • why you need where clause ,is it making any difference – Raushan Kumar Feb 07 '22 at 09:43
  • @RaushanKumar HI. The requested task is a report that performs a query (the one you see on), in the "WHERE" clause there must go optional parameters (expl: product code, planogram code, etc..). I entered 1=1 to be generic, but even entering the product code, the result does not change. – mike Feb 07 '22 at 09:56
  • @RaushanKumar when I remove this element ( {bs.uid} as 'Store', {ps.planogramCode} as 'Code Planogram' and JOIN BaseStore as bs JOIN PlanogramStore as ps on {bs.storeRef} = {ps.storeRef} AND {bs.bramchOffice} = {ps.branchOffice}) the query execute seccessful (oblivius without two column that I need for complete the task). – mike Feb 07 '22 at 10:05
  • 2
    Your statement contains errors. You join basestore with planogramStore. But neither planogram store, nor basestore is joined with any other part of your query. You need to join basestore or planogramstore with one of the other tables. – Yoni Feb 07 '22 at 10:33

1 Answers1

1

Your statement contains errors. You join basestore with planogramStore. But neither planogram store, nor basestore is joined with any other part of your query. You need to join basestore or planogramstore with one of the other tables.

Now you have 2 detached parts in your from statement, which is why you are getting errors

Product as p
JOIN PlanogramProducts as pp on {p.code} = {pp.productCode}
JOIN StockLevel as s on {pp.productCode} = {s.productCode}
JOIN EnumerationValue as evo on {p.status} = {evo.pk}
JOIN PlanogramCategory as pc on {pp.planogramCode} = 
 {pc.planogramCode}
JOIN PlamnogramCategoryAnag as pca on {pc.categoryCode}= 
{pca.categoryCode}

and

JOIN BaseStore as bs JOIN PlanogramStore as ps on {bs.storeRef} = 
{ps.storeRef} AND {bs.bramchOffice} = {ps.branchOffice}

you need to have a join between these 2 parts to get the correct data

Yoni
  • 1,370
  • 7
  • 17