community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

sql that works in sql developer but not in alteryx using in db tool

Meteor

Hello,

 

I've the code below that works just fine in sql developer but when I copy it to Alteryx it gives an error . Can someone shed some light on this, please?

 

code:

 

SELECT
a.ACCOUNT
,o.DESC
,o.BUSID
,a.NAME||' '||a.LNAME AS ACCNAME
,cur.ISOCODE
,bbp.GetBalanceAtDate(a.ID,to_date(to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) || ' 23:59:59', 'dd-mm-yy hh24:mi:ss')) BALINACCCUR
,DECODE(a.CURRENCYID, 100, bbp.GetBalanceAtDate(a.ID,to_date(to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) || ' 23:59:59', 'dd-mm-yy hh24:mi:ss')))
, Tools.ConvertToBase(bbp.GetBalanceAtDate(a.ID,to_date(to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) || ' 23:59:59', 'dd-mm-yy hh24:mi:ss'))
, a.CURRENCYID , TO_DATE('31-JAN-2011','DD-MON-YYYY')) AS GBPBALANCE
FROM
bbp.ACCOUNTS a
,bbp.ORIGINS o
,bbp.CURRENCIES cur
WHERE
a.CURRENCYID <> 1300
AND a.OriginID = o.ID
AND o.BUSID = 5
AND a.CURRENCYID = cur.ID
AND NVL(bbp.GetBalanceAtDate(a.ID,to_date(to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) || ' 23:59:59', 'dd-mm-yy hh24:mi:ss')),0) <> 0

 

 

error2.png

 

 

error1.png

 

Thanks in advance.

 

Kind Regards,

Susana

 

 

 

Bolide

is bbp.GetBalanceAtDate a user defined function call? strip down your statement and see if that's it.

 

Simon

Meteor

Hi Simon,

 

Thanks for your reply :)

The thing is, the same code, runs in different environments...sql developer, SAS (enterprise guide) and even in R..... so, not sure why just in Alteryx it would be an issue. Any idea??

 

Thanks.

 

Kind Regards,

Susana 

You're using it with an In-DB tool, right? 

Just to make sure I'm reading it correctly, which DMBS?

 

The issue might not be the SQL itself, but rather how it got into the Alteryx SQL editor.  Alteryx accepts data from a copy/paste strangely somtimes.

General practice I follow:

1. Don't directly copy and paste out of another tool into an Alteryx SQL editor.  Sometimes it will get pasted as Rich Text instead of Plain Text.

2. Don't directly copy and paste over RDP.  Same issue as above, but it all depends on where you are copying from.

3. Instead of a direct copy/paste, paste it to an intermediate location.  Generally Notepad running on the same machine you have Alteryx is the way to go.  Paste there first, then copy/paste to Alteryx SQL editor.

4. If all else fails, manually type it.

5. If manually typing it fails, flip over to the visual query builder.  Although that comes with its own set of nuances as well.

Atom

that is a huge problem in my opinion... I spend too much time trying to make the SQL code "FIT" to the Alteryx requirements! 

Moderator
Moderator

@svick

 

I've checked with our Support and Product team and they have confirmed that Alteryx uses the same format and language for SQL as the SQL developer. So the most likely source of this issue is that some of the values, such a pipes, contain a different numeric back-end value between programs, making the query rich text instead of plain text. This is more of a Windows issue then a Alteryx SQL editor issue so unfortunately there is nothing we can do at this point to alleviate this error. The best workarounds for this would be to paste your query in a notepad to return it to plain text rather then rich text like @patrick_mcauliffe mentions.

 

 

Labels