Difference b/w dlookup & dbquery ,lookup & extract

[venkatpavan]

Hi,

Can i get the exact differences b/w DBQUERY & DBLOOKUP & LOOKUP & EXTRACT.

If you can explain by giving a simple i will really appreciate that.

Thanks in Advance

 

[janhes]

DBLOOKUP

The DBLOOKUP function executes an SQL statement against a database. The SQL statement can be any permitted by your database management system or ODBC driver.

Syntax

DBLOOKUP (single-text-expression, single-text-expression, [ single-text-literal ])

Note There are two different ways to specify the arguments for DBLOOKUP.

Meaning 1

DBLOOKUP (SQL_statement, mdq_filename, database_name
)

The arguments for DBLOOKUP are:
SQL_statement - The first argument is an SQL statement, as a text string. This can be any valid SQL statement that is permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
mdq_filename - The second argument is the name of a database query file (.mdq) produced by the Database Interface Designer. It contains the definition of the database that the SQL statement is to be executed against. If the database query file (.mdq) is in a directory other than the directory of the map, the path must be specified.

Note The database query file (.mdq) is accessed at map build time and is not needed at runtime.

database_name - The third argument is the name of a database in the database query file (.mdq) as defined in the Database Interface Designer.

If used in this way, both the .mdq file name and database name must be literals.

Meaning 2

DBLOOKUP (SQL_statement, parameters)

The arguments for DBLOOKUP are:
SQL_statement - The first argument is an SQL statement, as a text string. This can be any valid SQL statement that is permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
The second argument is a set of parameters, either:

a) -MDQ mdqfilename -DBNAME dbname
The keyword -MDQ is followed by the name of the database query file (.mdq) produced by the Database Interface Designer. This .mdq file contains the definition of the database. If the .mdq file is in a directory other than the directory of the map, the path must be specified. The .mdq filename is followed by the keyword -DBNAME and the database name as specified in the Database Interface Designer.

Note Using this syntax, the .mdq file is accessed at runtime and must be present.

OR
b) -DBTYPE database_type [database specific parameters]
The keyword -DBTYPE is followed by a keyword specifying the database type (for example, ODBC or ORACLE) followed, optionally, by database-specific parameters.

Note This syntax does not use an .mdq file, because the database-specific parameters provide the information required to connect to the database. Refer to the appropriate Database Adapter document for detailed information on the database-specific parameters that can be specified.

When used with Meaning 2, DBLOOKUP must conform to these rules:

§ All keywords (for example, -DBTYPE) can be upper or lower case, but not mixed.

§ A space is required between the keyword and its value (for example, -DBTYPE ODBC)

§ The order of the keywords is not important.

§ All database-specific parameters are optional.

Returns

This function returns a single-text-item.
The DBLOOKUP function returns the results of the query in the same format as a query specified for a map input card, except that it does not include the last carriage return/linefeed. Because this information is removed, it is easier to make use of a single value extracted from a database.

Example

Assume that you have a table named PARTS that contains the following data:

Also assume that this database has been defined using the Database Interface Designer in a file named mytest.mdq and that the name of the database, as specified in the .mdq file, is PartsDB.

DBLOOKUP ( "SELECT PART_NAME from PARTS where PART_NUMBER =1","mytest.mdq", "PartsDB")

This example returns ¼" x 3" Bolt
Using Syntax 2, you can also specify the DBLOOKUP this way:

DBLOOKUP( "SELECT PART_NAME from PARTS where PART_NUMBER =1",
"-MDQ mytest.mdq -DBNAME PartsDB" )

In this example using Syntax 2, both the .mdq file name and database name are specified.
Or, this way, using Syntax 2 by specifying the database type and the appropriate database-specific parameters:

DBLOOKUP("SELECT PART_NAME from PARTS where PART_NUMBER =1","-DBTYPE ORACLE -CONNECT MyDB -USER janes ")

For more examples using the DBLOOKUP function, see the Database Interface Designer Reference Guide.

Uses

Use DBLOOKUP to execute an SQL statement when you want to execute a SELECT statement to retrieve a specific column value in a large table in a database using the value of another input, rather than defining the entire table as an input card and using the LOOKUP, SEARCHDOWN or SEARCHUP functions.
Use DBLOOKUP to execute an SQL statement when you want to execute a SELECT statement to retrieve a specific column value from a table or database that might vary based on a parameter file. Using Syntax 2 of the DBLOOKUP function would allow these parameters to be specified dynamically at run time.


DBQUERY

The DBQUERY function executes an SQL statement against a database. The SQL statement can be any permitted by your database management system or ODBC driver

Syntax

DBQUERY (single-text-expression, single-text-expression, [ single-text-literal ] )

Note There are two different ways to specify the arguments for DBQUERY. See Meaning 1 and Meaning 2 below.

Meaning 1

DBQUERY (SQL_statement, mdq_filename, database_name)

The arguments for DBQUERY are:
SQL_statement - The first argument is an SQL statement as a text string. This can be any valid SQL statement that is permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
mdq_filename - The second argument is the name of a database query file (.mdq) produced by the Database Interface Designer. It contains the definition of the database that the SQL statement is to be executed against. If the .mdq file is in a directory other than the directory of the map, the path must be specified.

Note The .mdq file is accessed at map build time and is not needed at runtime.

single-text-literal - The third argument is the name of a database in the database query file (.mdq) as defined in the Database Interface Designer.

If used in this way, both the .mdq filename and database name must be literals.

Meaning 2

DBQUERY (SQL_statement, parameters)

The arguments for DBQUERY are:
SQL_statement - The first argument is an SQL statement as a text string. This can be any valid SQL statement that is permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
parameters - The second argument is a set of parameters, either:

a) -MDQ mdqfilename -DBNAME dbname
The keyword -MDQ is followed by the name of the database query file (.mdq) produced by the Database Interface Designer. This .mdq file contains the definition of the database. If the .mdq file is in a directory other than the directory of the map, the path must be specified. The .mdq filename is followed by the keyword -DBNAME and the database name as specified in the Database Interface Designer.

Note Using this syntax, the .mdq file is accessed at runtime and must be present.

OR

b) -DBTYPE database_type [database specific parameters]
The keyword -DBTYPE is followed by a keyword specifying the database type (e.g., ODBC or ORACLE) followed, optionally, by database-specific parameters.

Note This syntax does not use an .mdq file, because the database-specific parameters provide the information required to connect to the database. Refer to the appropriate Database Adapter document for detailed information on the database-specific parameters that can be specified.

When used with Meaning 2, DBQUERY must conform to these rules:

¨ All keywords (e.g., -DBTYPE) can be upper or lower case, but not mixed.

¨ A space is required between the keyword and its value (e.g., -DBTYPE ODBC)

¨ The order of the keywords is not important.

¨ All database-specific parameters are optional.

Returns

This function returns a single-text-item.

If your SQL statement is a SELECT statement, this function returns the results of the query in the same format as a query specified as a map input card, including row delimiters and terminators, etc.

If your SQL statement is anything other than a SELECT statement, this function returns NONE.

Examples

Assume that you have a table named PARTS that contains the following data:


Also assume that this database has been defined using the Database Interface Designer in a file named mytest.mdq and that the name of the database, as specified in the .mdq file, is PartsDB

DBQUERY ( "SELECT * from PARTS" ,"mytest.mdq" , "PartsDB" )

This example returns 1|¼" x 3" Bolt<cr><lf>2|¼" x 4" Bolt<cr><lf> where <cr><lf> is a carriage return followed by a line feed.
Using Syntax 2, you can also specify the DBQUERY this way, where both the .mdq file name and database name is specified:

DBQUERY ( "SELECT * from PARTS" ,"-MDQ mytest.mdq -DBNAME PartsDB" )

The following example, using Syntax 2, specifies the database type and the appropriate database-specific parameters:

DBQUERY ( "SELECT * from PARTS" , "-DBTYPE ORACLE -CONNECT MyDB -USER janes -PASSWORD secretpw" )

In another example, assume that you have an input file containing one order record. To map that order to another proprietary format, you also have a parts table with pricing information for every part for every customer, a very large table. Rather than using the entire parts table as the input to your map, you might use the RUN function with a DBQUERY to dynamically select only those rows from the parts table corresponding to the customer in the order file, as follows:

RUN ( "MapOrder.MMC" ,"IE2" + DBQUERY ( "SELECT * FROM Parts WHERE CustID = " +CustomerNo:OrderRecord:OrderFile + " ORDER BY PartNo" , "PartsDB.MDQ", "PartsDatabase" ) )

Uses

Use DBQUERY to execute an SQL statement when you want to look up information in a database using a parameterized query that is based on another value in your data.
If your SQL statement is a SELECT statement, the DBQUERY function may be used, in conjunction with the RUN function, to issue dynamic SELECT statements whose results can be used as input to another map.

Use Syntax 2 of the DBQUERY function to execute an SQL statement when the database, table or other database parameters might vary — perhaps being supplied by a parameter file.

Copyright © 2003 by Mercator Software, Inc.


LOOKUP

The LOOKUP function sequentially searches a series, returning the first member of the series that meets a specified condition.

Syntax

LOOKUP (series-object-expression, single-condition-expression)

Meaning

LOOKUP (series_to_search, condition_to_evaluate)

Returns

This function returns a single-object.
Returns the first member of series_to_search for which condition_to_evaluate evaluates to TRUE. Returns NONE, if no member of series_to_search meets the condition specified by condition_to_evaluate.

Examples

LOOKUP ( Account#:Customer , Company Name:Customer = "ACME" )

This example returns the Account# of Customer whose Company Name is ACME.

LOOKUP ( Part#:RowBSelect , Model#:RowBSelect = ModelCode:Legacy & Serial#:RowBSelect > "123")

This example returns the Part# of DBSelect where the Model# in that row matches the ModelCode of Legacy and the Serial# is greater than "123".

Uses

Use LOOKUP to find an occurrence of an object that meets a certain condition.
LOOKUP performs a sequential search over series_to_search. Use LOOKUP if series_to_search is not ordered. Using SEARCHUP when that series is in ASCII ascending order or SEARCHDOWN when that series is in ASCII descending order can save time if the series being searched is large.
LOOKUP differs from EXTRACT in that LOOKUP returns the first member of series_to_search that meets the condition_to_evaluate, while EXTRACT returns all members (one at a time) of series_to_search that meet the condition_to_evaluate.

Copyright © 2003 by Mercator Software, Inc.


EXTRACT

The EXTRACT function returns each member of a series for which a specified condition is true.

Syntax

EXTRACT (series-object-expression, single-condition-expression)

Meaning

EXTRACT (series_to_search, condition_to_evaluate)

Returns

This function returns a series-object.
The result is each member of series_to_search for which the condition specified by condition_to_evaluate evaluates to TRUE. EXTRACT returns NONE, if no member of series_to_search has a corresponding condition_to_evaluate that evaluates to TRUE.

Exampless

EXTRACT ( PO:Transaction , Store# = LocationO:Transaction )

This example returns all POs, one at a time, whose Location is a particular Store#.

EXTRACT ( RowBSelect , ProcessFlag Column:RowBSelect = "Y" )

This example returns all Rows that have a ProcessFlag Column value of "Y".

Uses

Use EXTRACT whenever you need only particular members of a series returned—those that meet a certain condition. An example may be only POs that contain backordered items.

Note The EXTRACT function can only be used in a map rule. It cannot be used in a component rule.

Copyright © 2003 by Mercator Software, Inc.

 

[SeriesConsumer]

Could have been easier by just saying RTFM ;-)

 

[janhes]

But then he'd ask where to find it.:-D

 

[venkatpavan]

Hi Janhes,

I really appreciate your help...but these information i can find in online library...but if you can able to xplain in your own words that can be more helpful to me....sorry for troubling you......

Thanks a Bunch for youe help,
V

 

[SeriesConsumer]

- DBLOOKUP, DBQUERY: retrieve data from a database based on an SQL statement
- LOOKUP: lookup one value from a series of values, according to a condition to be met
- EXTRACT: extract a subsequence from a series of values, according to a condition to be met.

If you were more concrete about your problem, help could be more specific.

 

[venkatpavan]

Thank you Sir

 

[janhes]

Well I thought that's what the manual said.