I am doing some user exits in the Sterling SI mapper (version 5.2) where I
build some SQL strings and then pass them to oracle and use the results during
translation. This works just ducky but I have come across some horrid tables
put in by a consulting company years ago that have quote strings in the names
of the tables. (** see rant below) My first and second thoughts were beating
them (long gone - and they wonder why we don't hire them back...) and replacing
the tables but neither is viable. I am able to build sql strings for them in a
BP because the it doesn't mind me using single or double quotes as long as I am
consistent. I build the first half of the SQL with single quotes enclosing the
double quotes in the names so it retains them as text and the second half with
double quotes surrounding my string conditions in single quotes and when I say
sql = concat(//sql1, //sql2) it works.
However I don't want to do it in the BP anymore because the BP runs on an
interchange and this query is about the customer at the PO level. We have
companies that are two different customers to us but their data comes all in a
single interchange (Stage and Peebles for instance). This is why I want to do
it in the map after I decide who is the customer at the PO header.
Anyway, I can't seem to use single quotes to enclose the double quotes in an
extended rule or enclose the double quotes in more double quotes. Has anyone
tried this? I just need a hint of its possible or the heads up that I am
wasting my time trying.
This totally derailed my plan today.
This is the SQL that runs. The double quotes are part of the table and field
names
select t."data_validation_id"
from yaps.business_rule_config t, yaps.order_source o
where t."order_source_id" = o."order_source_id"
and t."data_validation_id" in (1, 2, 16)
and o."fk_lawson_customer_number" = '28481'
and o."fk_order_source_type_id" = 1
and o."active_status" = 'A'
The xpath for this is below.
Sql1 = concat('select t.','"','data_validation_id','"',' from
yaps.business_rule_config t, yaps.order_source o where
t.','"','order_source_id','"',' = o.','"','order_source_id','"',' and
o.','"','fk_lawson_customer_number','"',' = ',string(/ProcessData/LawsonCust),'
and t.','"','data_validation_id','"',' in (1,2,16) and
o.','"','fk_order_source_type_id','"',' = 1 and o.','"','active_status','"')
Sql2 = concat(" = '","A","'")
**RANT - who does that????? Good grief... If I'd have done that in school my
prof would have given me a fat F on principle
[Non-text portions of this message have been removed]
------------------------------------
...
Please use the following Message Identifiers as your subject prefix: <SALES>,
<JOBS>, <LIST>, <TECH>, <MISC>, <EVENT>, <OFF-TOPIC>
Job postings are welcome, but for job postings or requests for work: <JOBS> IS
REQUIRED in the subject line as a prefix.Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/EDI-L/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/EDI-L/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
