SQL Database

[CBIN]

Hi
I'm trying to pull up value from an SQL database using variable values in the SQL statement. Using the examples given in mercator tutorials, i'm not able to create type tree using variable values.
Can anyone give me some examples for this
Thanks a lot
CBIN

 

[BocaBurger]

If the variables are in the same elememt, then you can set the tree up easily, if the variable has different definitions, then the answer is no, use a DBLOOKUP.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[CBIN]

Thanks. Yes, I'm using DBLOOKUP.
When i directly give the value for the "Where" clause, the value is getting pulled up. But when i give the variable, i'm not getting any output.
My query looks like this
DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo =33208506", "MFSelect.mdq" , "EDIIN" )
I get the output.

If i replace the InvoiceNo with the variable, it looks like this
DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = " + Ih_Invoice_Num Field:.:invoice + ",""MFSelect.mdq" ,"EDIIN")

Ih_Invoice_Num Field:.:invoice - this field is text in type tree and i have defined InvoiceNo in the table as VarChar.
The actual InvoiceNo data will be alphanumeric.
What am i doing wrong.. i'm little bit new to the database mapping.. is anything wrong with the syntax??

Any help is appreciated. Thanks.

 

[BocaBurger]

Syntax error! Syntax Error! Pay big consulting fees....


Contact support )))

If you double quote the input item, you convert it to a text literal, not a variable.

Try:
where InvoiceNo = Ih_Invoice_Num Field:.:invoice ,"MFSelect.mdq","EDIIN")



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[CBIN]

oops..

DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = Ih_Invoice_Num Field:.:invoice,""MFSelect.mdq" , "EDIIN" )

DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = Ih_Invoice_Num Field:.:invoice","MFSelect.mdq" , "EDIIN" )

both did't work...
anyother suggestions...

 

[BocaBurger]

Add a -T to the command line to see what the error is. You could also wrap the DBLOOKUP with a valid /fail (lasterrormessage, lasterrorcode) and turn on the audit log to see what you get. If no help there, contact Ascential support.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[janhes]

DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = '" + Ih_Invoice_Num Field:.:invoice + "'"","MFSelect.mdq" , "EDIIN" )

 

[janhes]

Sorry 1 too many " in previous post.
Jan

DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = '" + Ih_Invoice_Num Field:.:invoice + "'","MFSelect.mdq" , "EDIIN" )

 

[CBIN]

Yes, i figured this out last night..

Even the following works

DBLOOKUP ( "SELECT BOL from TASN where InvoiceNo = " + Ih_Invoice_Num Field:.:invoice + "","MFSelect.mdq" , "EDIIN" )

Thanks BocaBurger and Jan for your help.

CBIN