Custom SQL statement with parameter Seguir

0
Avatar
Darren DeCoste

I wish to pass a parameter into the sql query in the WHERE clause

WHERE A.Field = 'Something'

What is the correct syntax for using a parameter that comes from the Data Entry Form, drop down list selected value.

Something like?

WHERE A.field = {dropdownlist.selectedvalue}

Anybody done this before?  Thank you for your help.

 

3 comentarios

0
Avatar
Shotaro Ito
Moderador
Acciones de comentarios Permalink

Create a query prompt (Ex. Q1) then insert query prompt name into where clause such as 

WHERE A.Field = 'Q1'

On form, you can place drop down box with item source linked to the query prompt (Q1)

Video below may helps.

https://support.seagullscientific.com/hc/en-us/articles/230880448-Using-a-dynamic-filter-Video-5-36-

1
Avatar
kevin fenwick
Acciones de comentarios Permalink

Thank you for this post, I just started doing labels with custom queries with variables and found @shotaro Ito was spot on in his method, but there is a slight syntax issue. Seems the query prompt variable needs to start with a "?"


SELECT
JH.jobnum
,MQ.OrderNum
,MQ.PartNum
,MQ.PartDescription
,MQ.Quantity
,MQ.IUM

FROM ERP.JOBHEAD JH (NOLOCK)
INNER JOIN ERP.MTLQUEUE MQ (NOLOCK)
ON JH.Jobnum = MQ.JobNum
WHERE JH.JobNum = '?Q1'

0
Avatar
Alex Armstrong
Acciones de comentarios Permalink

Thank you for this post.  I'm not a beginner to SQL or parameterized queries by any stretch - but I am unable to figure this one out within Bar Tender.  The "Query" Tab is not highlighted or accessible and I am unable to set up a query prompt.  When I install the '?Q1' into my custom SQL Statement (below) and push, "Okay" or the Browse Tab, I get all sorts of error messages - in fact, Bar Tender shuts down and I have to relaunch the software.

Questions:  True or False?  Parameterized queries are doable when using "Use Custom SQL Statement"?  and secondly, True or False? Is there documentation out there or a video that addresses this subject matter specifically?  Any help you can provide would be greatly appreciated.

My goal:  scroll down and replace WHERE "MShipLineDate"='6/24/2019' with '?Q1'.

Sincerely, Alex in Toledo. 

Status Update:  I decided to upgrade from 10.1 to 2016R8 Enterprise Automation.  That may help.  I'll report back.  Alex

 

Iniciar sesión para dejar un comentario.