Queryprompt On Sql 关注

0
Avatar
Legacy Poster

Hi

Im new to Seagull´s Bartender and i have this SQL query

SELECT T0.[ItemCode], T0.[ItemName], T0.[PicturName] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE
T0.[ItemName] = cast(?QueryPrompt1 as nvarchar)

But when i try to run it i always got

Bartender Error Message #3239

OLE BD has dectected an error 0x80040E14:
Unknow Errror
Error Message OLE DB:
IDispatch error #3092

i have tried to use the QueryPrompt1 with and without the cast clausule and the ? sign but always got the same Error, if i delete the QueryPrompt1 then i have no problem using the database , im running SQL Server 2008 R2

Any idea will be welcomed

Best Regards

5 评论

0
Avatar
Shotaro Ito
版主
评论操作 固定链接

Hi Incom,

To insert literal by query prompt input in custom SQL, quote query prompt name by ''.
[sql]...WHERE [ItemName] = '?QueryPrompt1'[/sql]

There might be a timing null data was input to query prompt. If the field doesn't take null,
Try Query prompt > More options > Minimum, set minimum by 1 and substitute by single space character.
Hope that helps.
0
Avatar
Legacy Poster
评论操作 固定链接

Hi,

 

My data type is an Integer, what is the syntax for the QueryPrompt?

My QueryPrompt is called: Quantity From

 

select * from inventory

WHERE quantity >= ?QuantityFrom

 

Does not seem to work

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Does the following work for you?

 

SELECT * FROM `inventory` WHERE `quantity` >= ?QuantityFrom

 

Also, is the name of your query prompt "Quantity From" or "QuantityFrom"? Because this will make a difference... Try creating a query prompt name without a space and test.

0
Avatar
Legacy Poster
评论操作 固定链接

I have this custom query with the following prompts.  I must be doing something wrong because when I go into Browse, the prompts don't appear so I can select specific styles.  If I change the wheres in the query to specific values the data will appear.

 

I did do it correctly some time ago but but server had to be rebuilt and my previous work was scrubbed.

 

Please help.  Most urgent.

 

Thanks,

 

Paul

 

 

 

Select si.style,
si.itemno,
si.dimension,
si.color,
si.label,
si.prepack,
si.size,
si.oldsku,
si.companycode,
si.division,
st.stylename,
st.merchgroupa,
sz.sizedesc,
co.colordesc

from styleitem as si
inner join style as st on st.companycode=si.companycode and st.division=si.division and st.style=si.style
inner join size as SZ on si.companycode=sz.companycode and si.size=sz.size
inner join color as co on si.companycode=co.companycode and si.color=co.color

where si.style='?style'
and si.dimension='?dimension'
and si.color='?color'
and si.label='?label'
and si.prepack='?prepack'
and si.size='?size'

0
Avatar
Shotaro Ito
版主
评论操作 固定链接

Make sure you have created query prompts (style, dimension, color, label, prepack, size).

Temporary disable custom sql and use query tab to create a simple query to make sure query prompts appear.

请先登录再写评论。