SQL query

[pizzaboy]

SELECT /*+ ALL_ROWS */
product_foods.discipline,
product_foods.department,
product_foods.department_name,
fd_store_prod_dy.sku,
fd_store_prod_dy.product,
product_foods.product_name,
fd_store_prod_dy.boh_qty,
fd_willem.delivered_qty
FROM location,
product_foods,
dim_calendar,
fd_store_prod_dy,
OUTER fd_willem
WHERE fd_store_prod_dy.store = location.store
AND fd_store_prod_dy.sku = product_foods.sku
AND fd_willem.store = location.store
AND fd_willem.sku = product_foods.sku
AND fd_store_prod_dy.trading_year = dim_calendar.old_trading_year
AND fd_store_prod_dy.trading_week = dim_calendar.fin_week
AND fd_store_prod_dy.trading_day = dim_calendar.fin_day
AND fd_store_prod_dy.trading_year = 2007
AND fd_store_prod_dy.trading_week = 7
AND fd_store_prod_dy.trading_day = 2
AND location.store=103
AND location.store=#prompt ( "pLocation", "integer", "-1")#
-- AND dim_calendar.calendar_date=DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)-1
-- AND fd_willem.trading_date=DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)
AND fd_store_prod_dy.boh_qty <= 0
AND fd_store_prod_dy.cataloged > 0
AND product_foods.department not in (20,32,33,49,70,75,18,23,24,27,28,29,30,31,60,61,67,76)
AND product_foods.multi_pack_ind = 0
AND exists
(SELECT 1
FROM fd_store_orders f
WHERE f.store = #prompt ( "pLocation", "integer", "-1")#
-- AND f.trading_date = DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)-2
AND f.boh1_ind <> 'N'
AND product_foods.sku = f.sku
)


pls assist me. I get an error and im not sure why?

[Slasher]

1) What error

2) What should this query do...

3) What does your database structure look like? Is all the tables and links and columns defined correctly in the code?

[pizzaboy]

ok I have commented below via "--" to tell you which lines have problems. I suspect "#prompt" is the culprit...

SELECT /*+ ALL_ROWS */
product_foods.discipline,
product_foods.department,
product_foods.department_name,
fd_store_prod_dy.sku,
fd_store_prod_dy.product,
product_foods.product_name,
fd_store_prod_dy.boh_qty,
fd_willem.delivered_qty
FROM location,
product_foods,
dim_calendar,
fd_store_prod_dy,
OUTER fd_willem
WHERE fd_store_prod_dy.store = location.store
AND fd_store_prod_dy.sku = product_foods.sku
AND fd_willem.store = location.store
AND fd_willem.sku = product_foods.sku
AND fd_store_prod_dy.trading_year = dim_calendar.old_trading_year
AND fd_store_prod_dy.trading_week = dim_calendar.fin_week
AND fd_store_prod_dy.trading_day = dim_calendar.fin_day
-- AND location.store=#prompt ( "pLocation", "integer", "-1")#
-- AND dim_calendar.calendar_date=DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)-1
-- AND fd_willem.trading_date=DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)
AND fd_store_prod_dy.boh_qty <= 0
AND fd_store_prod_dy.cataloged > 0
AND product_foods.department not in (20,32,33,49,70,75,18,23,24,27,28,29,30,31,60,61,67,76)
AND product_foods.multi_pack_ind = 0
-- AND exists
-- (SELECT 1
-- FROM fd_store_orders f
-- WHERE f.store = #prompt ( "pLocation", "integer", "-1")#
-- AND f.trading_date = DATE(DATETIME(#prompt ( 'pDate', 'date', '2007-01-01')#) YEAR TO DAY)-2
-- AND f.boh1_ind <> 'N'
-- AND product_foods.sku = f.sku
-- )



This code was tooken from a cognos report. The tables are correct. if I take the comments out, and when running the query it gives me this error: Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)

[Hex_Rated]

What db engine? I googled your error and it looks like IBM informix? Have you looked for the error code in the documentation? This guy had the same error:

http://www.dbtalk.net/comp-databases-in ... 39826.html

[pizzaboy]

it is IBM informix.

tried to register with that site but it seems that they wont allow me to register...it says disabled by admin.

[pizzaboy]

hi

the problem was the #prompt....#.

this is only recognised from cognos( i took the SQL data from a model in the report i was working on)....