Dynamic input

[Abdelrahmanmahdy]

Hi All,

I want to have a dynamic input to fetch the data from one SQL table.

I want from Alteryx server to insert the PO number but every time I have the same output and I dont Know why.

You can find in the images attached the workflow.

My query is simple 

DECLARE @PONumber VARCHAR(20)
DECLARE @Currency VARCHAR(3)
DECLARE @Amount VARCHAR(20)
DECLARE @Exchange_rate VARCHAR(20)

SET @PONumber = '35452918'
SET @Currency = 'EUR'
SET @Amount = '945200'
SET @Exchange_rate = '1'

SELECT order_number AS PONumber, orders_currency AS Currency , price_ordered AS Amount , orders_exchangerate AS Exchange_rate

FROM T2_PROD_NEM_NEW.trimergo.orders

WHERE order_number = @PONumber AND orders_currency = @Currency AND price_ordered =@Amount AND orders_exchangerate = @Exchange_rate

[ChrisTX]

Are you running the app with the Run button or the magic wand?

In the second screenshot: Guessing you have the right line selected (highlighted in Blue)?

Chris

[Abdelrahmanmahdy]

I am running the app from the run button. the second image shows option replace the PO number but it doesn't affect anything.

[ChrisTX]

You said

the second image shows option replace the PO number but it doesn't affect anything

Do you know that in the top section, the line highlighted in Blue is the text that will be changed?  do you have the correct line selected (highlighted in Blue)?

[Abdelrahmanmahdy]

I know that the blue selected line is the one I have to change.

I have attached four images. The first three show the steps I did in the action tool.

First one when I connect the action tool, second one when I check the replace a specific string and the third one shows when I delete all the query and keep only the number 35452918.

The forth image shows the text box whatever I write a new PO the out put still 35452918.

[ChrisTX]

I think I'll need to see the entire workflow and macro.  I can't understand the flow with just the screenshots. 

In the first image, you're trying to replace a specific string that doesn't exist in the highlighted line.

[Abdelrahmanmahdy]

You can find the workflow as an attachment.

[ChrisTX]

What happens when you run the app with the magic wand and type in a different value for the PO number?  Do you not get any results?

Because you are only replacing one value (the PO Number) in your query, but the SQL has four hard-coded values:

SET @PONumber = '35452918'
SET @Currency = 'EUR'
SET @Amount = '945200'
SET @Exchange_rate = '1'

Chris

[Abdelrahmanmahdy]

When I put in the text box a new value for PO. it still gives me the same four values in the output (35452918,EUR,945200 and 1)

When I keep only SET @PONumber = '35452918' and remove the other three values from the query as below it doesn't give me any values.

DECLARE @PONumber VARCHAR(20)
DECLARE @Currency VARCHAR(3)
DECLARE @Amount VARCHAR(20)
DECLARE @Exchange_rate VARCHAR(20)

SET @PONumber = '35452918'

SELECT order_number AS PONumber, orders_currency AS Currency , price_ordered AS Amount , orders_exchangerate AS Exchange_rate

FROM T2_PROD_NEM_NEW.trimergo.orders

WHERE order_number = @PONumber AND orders_currency = @Currency AND price_ordered =@Amount AND orders_exchangerate = @Exchange_rate

[ChrisTX · Accepted solution]

Open the app in designer so you can see the workflow.  Open Interface Designer, click the magic wand, and click the Debug button

Enter a new value for a PO Number. 

when the Debug tab opens, see what value is in your SQL.

Also in the Debug tab, what is in the Debug log at the top right.

This statement:

When I keep only SET @PONumber = '35452918' and remove the other three values from the query as below it doesn't give me any values.

is because your SQL WHERE clause is expecting 4 fields to be set, but you've only set one.

This is a basic SQL issue, not related to Alteryx.