Dynamic Input SQL Update Where Clause

[zebing]

I'm having some trouble with the dynamic input tool update my SQL where clause. I can do this just fine using a spreadsheet that feeds into my dynamic input tool. The spreadsheet has a field/column named "DEPT_NBR". When I use the "Update Where Clause" feature as shown in the attached picture (modify 1), everything works just fine. Here is the SQL of the where clause.

WHERE "DEPT_NBR" IN ('X')

Now, when I try to do this for an AND, things don't work. Here is the SQL:

WHERE "DEPT_NBR" IN ('X')
AND
"BRAND_ID" IN ('Y')

And here is the error I'm getting:

"Only one action may specify Group for SQL IN"

And the second image I've attached shows how I have updated the second where clause.

Can anyone tell me why I'm only being allowed to update one part of my where clause? How can I update the second part with the AND statement?

modify 1modify 2

[bsharbo]

Hello.

Short answer  simply un-check the "Group replacement value for SQL in Clause" on one of your two update statements. 

Longer Explanation: When you check the "group replacement value for SQL in clause" option, it tells Alteryx to put all of your distinct list of Values from your input tool into one query using an IN () clause.

For Example: Pretend you only had to update ONE of your IN () clauses in your where clause  WHERE "DEPT_NBR" IN ('X').

If you had an input flow that had 50 department numbers, and you use an IN clause this way and you DO NOT CHECK  the "gropuing replacment value for SQL in clause" then alteryx will do 50 individual passes to the database of that query.  Aka it will do.

select * from TABLE

WHERE "DEPT_NBR" IN ('A').

select * from TABLE

WHERE "DEPT_NBR" IN ('B').

....

select * from TABLE

WHERE "DEPT_NBR" IN ('Z').

When you check this option, it groups all of your list of 50 values into ONE Query, so it only hits the database once (which is much much better performance wise).  Aka you query looks like

select * from TABLE

WHERE "DEPT_NBR" IN ('A','B','C'.......,'Z').

Why this matters:

It may not be immediately obvious, but it is logically not correct to have this process work on two independent columns in all instances, and that is why alteryx does not allow it (I assume).

For Example:   Say that you had a database where you had Stores and Brands, and every single store had data for every single Brand, since every store had the ability to sell each brand  (Aka your database table looked like below; and you have 3 stores and two brands.).

Store	Brand
Denver	Coke
Denver	Pepsi
Chicago	Coke
Chicago	Pepsi
New York	Coke
New York	Pepsi

If you wanted to pull from the system the follow records.

You want to pull the following rows

Store	Brand
Denver	Coke
Denver	Pepsi
Chicago	Coke

Note: You only want the COKE Brand from the chicago store (for whatever reason).  

If you did the group by in clause on Both the Store and brand, alteryx would logically change your query too

select * from table where Store in ("Denver","Chicago") and Brand in ("Coke","Pepsi").

As you can see this is NOT correct, because this would also return the row of Chicago and Pepsi, which you explictily did not want.

So the reason that this is designed to fail in alteryx is because logcially it doesn't make sense in the first place!

[zebing]

Hey, thanks so much for the reply. I think this gets me closer. I've stopped getting the previous error I was receiving which read "Only one action may specify Group for SQL IN".

I've started getting a different error. The error now reads "Error SQLPrepare: [Teradata][ADBC Teradata Driver][Teradata Database] A Character string failed conversion to a numeric value".

I was getting this error earlier before I posted my original question. I fixed it by changing the "value type" field in the info box shown in the images I posted earlier. I changed the "value type" from text to integer and made sure that the column was formatted as a number in the excel spreadsheet I was pulling from. This no longer seems to work now that I have more than one "SQL Update Where Clause". Trying various combinations of formatting the excel list as text / numbers and changed the "value type" field don't seem to make this error go away. I'm not sure why I could make it go away when I had one "SQL update where clause" but not when I have two.

Any ideas?

[Coxta45]

@zebing,

In my opinion, the "Replace a Specific String" option is a much better approach.  It never fails (when configured properly) and you can essentially add as many rules as you'd like.

Assuming you have your DEPT_NBR's and BRAND_ID's in the workflow and feeding the dynamic input tool...

You would write your query like so...

And all that's left to do is add the rules that you need with the "Replace a Specific String" option...

Viola!  This always works for me.  Just make sure you adjust any string quotes, etc. if your DEPT_NBRS and BRAND_ID's are integers.  You'd simply remove the single quotes where necessary.  Hope it helps!

Taylor

[AmNotARobot]

Thank you Taylor, your example was very clear and as a consequence very helpful. Cheers.

[chrissgtan]

Thanks, your tip works for me