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

[Su]

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

Thanks in advance.

Kind Regards,

Susana

[simon]

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

Simon

[Su]

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 

[patrick_mcauliffe]

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.

[svick]

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

[KylieF]

@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.

[Spatel68]

I did copy my query from Oracle SQL Developer to Notepad and then pasted in alteryx SLQ Editor.  Still giving my error.  I am performing some join and passing variable between outer query and inner query.  Rewriting in alteryx SQL editor is extra work and hinders efficiency.  Is there a some type of open source code converter?

[jpoz]

What version of alteryx are you using?

[Spatel68]

2019.2.7.63499

[jpoz]

We have made some improvements to the SQL editor since that release. Would you be willing to try a newer release and see if the issue still persists?