data manipulation in SQL Passthrough query

  • Thread starter Thread starter Andreas
  • Start date Start date
A

Andreas

Hello,

I have a pass-through query (seen below). The field
"fpmcaps.x_fpmrequestdata.data" is kind of a data blob that contains
various fields. I am trying to split the blob (see middle section), but
access gives me the error message that the right parenthesis is
missing.

SELECT fpmcaps.x_fpmrequest.receivername,
fpmcaps.x_fpmrequestdata.data,(fpmcaps.x_fpmrequest.settlementamount/100)
AS Amount, fpmcaps.x_fpmrequest.sendername AS sender,

IIf(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')=0,Null,Mid(fpmcaps.x_fpmrequestdata.data,(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')+17),(InStr(1,fpmcaps.x_fpmrequestdata.data,'</beneficiaryName>')-(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')+17))))
AS BeneName

FROM fpmcaps.x_fpmrequest INNER JOIN fpmcaps.x_fpmrequestdata ON
fpmcaps.x_fpmrequest.inputid = fpmcaps.x_fpmrequestdata.request_inputid

WHERE fpmcaps.x_fpmrequest.statename =
'IN_WORKFLOW/DEEP_IN_WORKFLOW/SANCTION_HOLD' AND
fpmcaps.x_fpmrequest.customproperty1 = 'FP'

Anyone any comments on why I would receive this message?

Thanks,

Andreas
 
That's horrible, why not write a VBA function instead of that hideous Iif
function, it would be much easier to debug.

What makes you say it's a passthrough query? I don't see how it can be with
all those VBA functions.

Andreas said:
Hello,

I have a pass-through query (seen below). The field
"fpmcaps.x_fpmrequestdata.data" is kind of a data blob that contains
various fields. I am trying to split the blob (see middle section), but
access gives me the error message that the right parenthesis is
missing.

SELECT fpmcaps.x_fpmrequest.receivername,
fpmcaps.x_fpmrequestdata.data,(fpmcaps.x_fpmrequest.settlementamount/100)
AS Amount, fpmcaps.x_fpmrequest.sendername AS sender,
Click to expand...
IIf(InStr(1,fpmcaps.x_fpmrequestdata.data,'<beneficiaryName>')=0,Null,Mid(fp
mcaps.x_fpmrequestdata.data,(InStr(1,fpmcaps.x_fpmrequestdata.data,'<benefic
 
Back
Top