Hi there,
I am trying to code a select statement in a machine step with a where clause using ‘like’:
e.g.
where lot_batch_no like ({v_entered_po_no}||‘%’)
I’m sure this used to work when we were on an older version of Flow and the sql statement works fine in PL/SQL but now I just get an ORA-00904 error:
Any help would be most appreciated.
Gail
Hi, take a look at Query SQL in odd ways | Extensibility and Integration
Its for MS SQL, but it works with oracle as well
Thanks for that but when I type @ it blocks it out in purple:
In notepad it shows as:
Declare @SQL nvarchar(max);
Declare @POno nvarchar (max);
Set @POno = {vPONo};
Set @SQL = ’
select a.handling_unit_id “HU_ID”
from ifsapp.INVENTORY_PART_IN_STOCK_UIV a
where a.lot_batch_no like ‘’‘+ @POno + ‘%’’’
Exec (@SQL);
I’ve still maybe not coded it quite correctly and getting this:
Hi,
just wrap the value of the variable in ’ ’ and it should work (tried it in 6.15 with Database connector / IFS Applications 10 type)
Hope this helps!
B R
Ivan
Thanks for that Ivan, however the plot thickens…
That works for me too when I pass in a completely numeric value. However, when I add the P in front of the number I get the invalid identifier error:
I have checked and I’ve definitely set the field up as text input:
And the field I am selecting against is definitely a varchar2.
Any ideas?
many thanks,
Gail
Hi,
hmmm not sure why it does that for you, for me it doesn’t matter if its a letter or a number
What kind of connector type are you using: (oracle 10.2 or one of the .net IFS Applications…)
(Maybe there is a bug there)
Can you run my example flow with your connector?
Test Like.flow (379.7 KB)
B R
Ivan
That’s brilliant thanks - I had missed the assignment step - all sorted now - thanks so much!
