We are attempting to do a lookup against a record that we can see in our database and the incoming file contains data that should match (at least by all visiual inspections). The statement that follows is what is in the map:
=DBLOOKUP
( "SELECT taxpayer_id FROM adm_app.entity_contact_info " +
"WHERE taxpayer_id = " + BTI08 IDCd Element:IDCd_1 MComposite:BTI Segment:Transaction #813 Inbound Partner Set V4030:#813<>F4030<>Inbound Partner Funct'lGroup ANSIartner X12 Inbound Interchange:813v4030 +
" AND to_char(period_end_dtm, 'DD-Mon-YYYY') = '" +
FROMDATETIME
( Date ElementTM_194 DTM Segment:Transaction #813 Inbound Partner Set V4030:#813<>F4030<>Inbound Partner Funct'lGroup ANSIartner X12 Inbound Interchange:813v4030, "{DD-MON-CCYY}" ) + "'" +
" AND sender_id = " + Sender_ID Fields:COMMON +
" AND ( submission_type = '00' or submission_type = '05' or submission_type = '15' )",
"-MDQ %exstars_mdq% -DBNAME exstars" )
What we have found by trail and error is that the problem appears to be with our Sender_ID field. The content of that field both in the database and in the incoming file is 02-4941077. With the dash in the field we get no match but however if we remove the dash from the data populated in the database and in the incoming file we get a match. We've checked field definitions and it is defined as a text/character accross the board.
Any ideas or suggestions to try so that the we will get a match with the dash left in the data?
=DBLOOKUP
( "SELECT taxpayer_id FROM adm_app.entity_contact_info " +
"WHERE taxpayer_id = " + BTI08 IDCd Element:IDCd_1 MComposite:BTI Segment:Transaction #813 Inbound Partner Set V4030:#813<>F4030<>Inbound Partner Funct'lGroup ANSIartner X12 Inbound Interchange:813v4030 +
" AND to_char(period_end_dtm, 'DD-Mon-YYYY') = '" +
FROMDATETIME
( Date ElementTM_194 DTM Segment:Transaction #813 Inbound Partner Set V4030:#813<>F4030<>Inbound Partner Funct'lGroup ANSIartner X12 Inbound Interchange:813v4030, "{DD-MON-CCYY}" ) + "'" +
" AND sender_id = " + Sender_ID Fields:COMMON +
" AND ( submission_type = '00' or submission_type = '05' or submission_type = '15' )",
"-MDQ %exstars_mdq% -DBNAME exstars" )
What we have found by trail and error is that the problem appears to be with our Sender_ID field. The content of that field both in the database and in the incoming file is 02-4941077. With the dash in the field we get no match but however if we remove the dash from the data populated in the database and in the incoming file we get a match. We've checked field definitions and it is defined as a text/character accross the board.
Any ideas or suggestions to try so that the we will get a match with the dash left in the data?