DBLOOKUP Question

[Stella001]

I am having problems with the query below. I need to pass in thw two elements of SCHED_NO and SHIPMT_NO in order for me to get back the correct information. This is not working the way I am passing them. Any help would be appreciated.


=EITHER( DBLOOKUP( "Select olsi.inst_txt from ord_ln_sched_inst olsi, shipmt_ln sl where sl.ord_no = olsi.ord_no and sl.ord_ln_no = olsi.ord_ln_no and sl.seq_no = olsi.seq_no and olsi.inst_no = 1 and sl.shipmt_ln_no = and sl.shipmt_no = " + TRIMLEFT ( SCHED_NO ALPHA FIELD DATA:KEY FIELD_GROUP:E010_ASN_SHIPMT_LN_TABLE RECORD:ASN_SHIP_LINE , "0" ) + TRIMLEFT ( NUMBERTOTEXT ( SHIPMT_NO SHORT_NUMERIC FIELD DATA:B020_ASN_SHIPMT_TABLE2 RECORD:SHIPMENT ) , "0" ) , "-DBTYPE ORACLE -CONNECT ptmn -USER ***** -PASSWORD *****" ) , "@NO_STORE@" )

 

[RichLee]

Hi

It looks like your SQL statement is incorrect, try...

=EITHER( DBLOOKUP( "Select olsi.inst_txt from ord_ln_sched_inst olsi, shipmt_ln sl where sl.ord_no = olsi.ord_no and sl.ord_ln_no = olsi.ord_ln_no and sl.seq_no = olsi.seq_no and olsi.inst_no = 1 and sl.shipmt_ln_no = “ + TRIMLEFT ( SCHED_NO ALPHA FIELD DATA:KEY FIELD_GROUP:E010_ASN_SHIPMT_LN_TABLE RECORD:ASN_SHIP_LINE , "0" ) + “ and sl.shipmt_no = " + TRIMLEFT ( NUMBERTOTEXT ( SHIPMT_NO SHORT_NUMERIC FIELD DATA:B020_ASN_SHIPMT_TABLE2 RECORD:SHIPMENT ) , "0" ) , "-DBTYPE ORACLE -CONNECT ptmn -USER ***** -PASSWORD *****" ) , "@NO_STORE@" )

Richard.

 

[eyetry]

if that doesn't work try this...

=EITHER( DBLOOKUP( "Select olsi.inst_txt from ord_ln_sched_inst olsi, shipmt_ln sl where sl.ord_no = olsi.ord_no and sl.ord_ln_no = olsi.ord_ln_no and sl.seq_no = olsi.seq_no and olsi.inst_no = 1 and sl.shipmt_ln_no = '" + TRIMLEFT ( SCHED_NO ALPHA FIELD DATA:KEY FIELD_GROUP:E010_ASN_SHIPMT_LN_TABLE RECORD:ASN_SHIP_LINE , "0" ) + "' and sl.shipmt_no = '" + TRIMLEFT ( NUMBERTOTEXT ( SHIPMT_NO SHORT_NUMERIC FIELD DATA:B020_ASN_SHIPMT_TABLE2 RECORD:SHIPMENT ) , "0" )+"'" ) , "-DBTYPE ORACLE -CONNECT ptmn -USER ***** -PASSWORD *****" ) , "@NO_STORE@" )

Also, if you add this to the connection string it should generate a trace file that will display your query and related return code...

-trace select_inst_txt.dbl

 

[Stella001]

thank you for the quick answers. Both ways actually worked.