Simple Oracle Mapping Question (DID/DBQUERY/etc) 2

[myuserid7]

I have read through piles of documents and searched many forums, but I am on a short deadline and am incapable of figuring this out for some reason. I apologize in advance for what I am sure will be a very simple question to those who have Mercator experience.

I need a map to merge 2 sets of data, one from a file, one from a db. This is a very simplified example.

Input 1: File
ID, metadata1, metadata2

Input 2: Database
ID, title, desc

Output: ID, title, desc, metadata1, metadata2

However, in my output all of the inputs are merged with all of the outputs, not synched up by ID. I had expected this because I cannot figure out how to restrict it based on ID. I have read about using #VAR# in the SQL statement and changing the value at runtime, but I would think there has to be a way using DBQUERY.

Any assistance would be greatly appreciated.

 

[janhes]

if your two input record sets contain the same number of records and ids in the same order you could use the choose function

f_make_rec(inputrecord1,CHOOSE ( inputrecord2, INDEX ( Inputrecord1)))

otherwise you coud use the lookup or extract function.

See exercise 13 & 14 in the tutorial - manual 1027.pdf

 

[eyetry]

Doesn't the choose function assume there are an equal number of recs in the file with matching IDs and sorted in same order?

If the DB data is ordered by ID you could also use function SEARCH(DOWN/UP).

 

[janhes]

CHOOSE picks the nth object from a series.
It only works in the above if both files contain the same number of records in the same order with matching IDs

I think SEARCH(UP/DOWN) would be less efficient as it has to search through increasing amounts of data each time.

 

[myuserid7]

The database does not contain the same number of records as the file, nor will they be in the same order. So I do need something other than CHOOSE.

I had hoped there would be a way to do something like

DBQUERY with a where clause, like

select title, desc where id = ID:In1

and have it populate the output card. Is this possible?

 

[janhes]

In that case pass In1 record and the ID as a variable to a query to a second map where you do the mapping.
eg

VALID(
RUN("map2.mmc",
"-ID2 '-VAR ID=" + 
+ ID:In1 + " -TE+'" + echoin(1,package(In1_rec)) + " -OE1"),fail(
"Failed To Run map2.mmc" + TEXT ( LASTERRORCODE ( ) ) + "):" + LASTERRORMSG ( ) ))

The query run by card 2 in map 2 is

call storedprocedure(#ID#,?,?,?,?,?,?,?,?)

 

[myuserid7]

Thanks for the help janhes, that sounds promising.

Is there no way to build a functional map with an Input Card of type db_definition.mtt and then call it from the main map with something like:

f_mergeData( Item:In1, DBQUERY("select * from table where id = " + ID:In1) )

 

[BocaBurger]

Functional maps need defined inputs (with type trees). There is no definition for DBQUERY.



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

 

[eyetry]

janhes - agree for large file search has performance issues on larger files. I like the runmap option. would need to have the runmap output as append though.

hehe.... This might work for you in an fmap....

MAKE_RECORD_OUT(FILE_ID IN:IN1,
TEXT(DBLOOKUP ("SELECT title ||'" + "|" + "'|| desc FROM table_name WHERE ID = '"+ TEXT(FILE_ID IN:IN1) + "'",
" -MDQ path\dbid_file.mdq -dn dbid_name -trace TEST_QUERY.dbl" ))

fmap input card 2 = text blob.

Then in the rules would read...

title_fieldut = word(in2, "!|", 1)
desc_fieldut = word(in2, "!|", -1)

Aint pretty and would hate to do this for a complicated query but..... it works.

 

[janhes]

myuserid7
You could do
f_mergeData( Item:In1, DBQUERY("select * from table where id = " + ID:In1) )
but remember to use the -mdq and -dbname options
in your functional map you could have the output rule as
=substitute(In2,"|",",") + "," + metadata1:In1 + "," + metadata2:In1

You need the substitute to replace the | returned as a delimiter in the dbquery.

However this aproach does not give you much control over manipulation of the dbquery data.
Also, if the query returns no data, the functional map won't run.

 

[myuserid7]

I have tried to go with running a second map, but am having some difficulty.

In my primary map output card, I have

record=RUN("testdb.mmc", "-VAR ID= " + PACKAGE(ID:Record:FileInput) + " " + ECHOIN(2, PACKAGE(Record:FileInput)) + " -OE1"))

However the build fails complaining "Output argument of rule does not match output type"

The output card of testdb.mmc is the same type as "record", so I'm not sure why I would get this unless -OE1 does not properly return the output.

I have read through a lot of documentation and see a lot of examples of the RUN command but none showing how to use it to return a record or list of records.

Any help would be greatly appreciated!

 

[eyetry]

Think it's your punctuation...

record=RUN("testdb.mmc", "' -ID1 '-VAR dbid_var_name=''" + ID:Record:FileInput + "'" + ECHOIN(2, PACKAGE(Record:FileInput)) + " -OE1"))

 

[myuserid7]

eyetry,

I tried plugging in what you recommended:

Record(s) =RUN("testdb.mmc", "' -ID1 '-VAR ID=''" + ID:Record:FileInput + "'" + ECHOIN(2, PACKAGE(Record:FileInput)) + " -OE1")

When I removed "PACKAGE" from around + ID:Record:FileInput I get "ID:Record:FileInput is an invalid operand."

But I also still get "Output argument of rule does not match output type"


FileInput is the name of my input card, it looks like this:

FileInput
- Record(s)
+ ID
+ Metadata1
+ Metadata2

My output card is named MergedOutput and looks like this:

MergedOutput
- Record(s) = RUN (...)
- ID <greyed out>
- Title <greyed out>
- Desc <greyed out>
- Metadata1 <greyed out>
- Metadata2 <greyed out>

and testdb.mmc has a db query input card, and a record input card and outputs a single merged record.

Maybe I am doing something wrong with all of this?

Thanks so much for your help.

 

[OllyC]

RUN () will return a text object - you'll need to pass this to a functional map to build the individual ID, title etc. fields.

e.g.
Record(s) =fmap(RUN("testdb....))

Olly.

 

[eyetry]

If you contiue to ge this failure make sure you have the trace and logs turned on. Include the DB trace in the runmap.

 

[janhes]

Put the RUN in a card with a single test item with no max size.