Originally posted by: SystemAdmin

Hi,

I am very new to this, here is my issue and help me if you have any information.

I have a simple query to pull data from an Oracle 10g database. I have created a mdq fill with the following query.

select caseno from table where PRGCD = #Type_CD#

I have defined the value(within single quotes) for this variable in the Define Variable window.

I have defined the following in the input card of the map, under GET,

Source : Database
Databasequeryfile : tt.mdq and choose the query that I had created under this mdq file.

The issue is, when I run the map I am getting a "Source not available" error. If I run this query without the variable(select caseno from table where PRGCD = 'MN'), it works fine.

Please advise me

Thanks
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

The command line also needs to include what the name of the table is:

-VAR name_of_var=value

There is an example map on this thread: http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?message=13935428&cat=9&thread=156531&treeDisplayType=expandtree&forum=829#13935428

Just remember that the variables set in the Database Interface Designer are only for use within Database Interface Designer. They are not available outside of there because, well, there wouldn't be much point to it since you want to be able to have the variable be variable.

Yvonne Ricard
Resistance is NOT futile.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Thanks a lot. It worked. I will ask you another question(Oracle 10g).

in the MDQ file I have a query, which calls a stored procedure. I am getting "Source not available" message

ex.
query name :Myquery
call mySP()

My inputcard GET function
Source: Database
Command: -USER xxx -PW xxx

DatabaseQueryFile
File: Myquery.mdq
database: mydatabase
Query: myquery

Am I making any mistake here? I appreciate your response.

Thanks

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

It looks right. Are you sure you have access to that stored procedure? Is it under the schema of the user you are specifying for "-USER"?

To get more information you might want to add a -T to the command line. This will generate a *.dbl file. The dbl file is the database trace and shows the adapter loading, connecting to the database, making the call, etc. It should give you a better indication as to what is actually going wrong. "Source not available" is the generic map response when anything goes wrong with an input adapter. The adapter trace gives you more specific information.

Yvonne Ricard
Resistance is NOT futile.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I have to check the permission etc with db team. Mean while I have another question

I have to pass a date(as -VAR) to my Query(in mdq file). This is what I am doing.

currently my 2nd inputcard get source looks like

GET:Source:command: -USER xxxx -PW xxxx -VAR IN_DATE='04-apr-2007' -T

I have to replace this hardcoded date with current date.

My 1st outputcard(name:OUTPUT_DATE) gets the currentdate(=FROMDATETIME(CURRENTDATE(), "dd-mon-ccyy")). Its writting to a text file successfully.

Can I do this? I tried it did not work.

GET:Source:command: -USER xxxx -PW xxxx -VAR IN_DATE=SYS_DATE Field:OUTPUT_DATE -T

Will "SYS_DATE Field:OUTPUT_DATE" field will hold the value and can be used in the 2nd input card? Will the 2nd input card wait till the 1st output card finish processing?
I appreciate your response.

Thanks
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

You can't pass variable data to the input cards. All input cards are processed before output cards are processed. This order is likely why what you are trying does not work.

My suggestion would be to use a RUN map as described in my other post to get the data you need. That other post has an example map that uses the VAR option in the manner you are attempting.

Yvonne Ricard
Resistance is NOT futile.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: jvanboga

What did your trace file say?

Also, take the query as it appears in the trace file and submit it manually and see what your results are. It could be a number of things; authorization, date format, punctuation.....

#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: rarw

Hi,

I just started working on this problem with the original person in the post (mrm). I have expereince creating maps in WTx and processing cobol files; this is my first ORACLE database task.

I have gotten Yvonne Ricard's example to work on our ORACLE database -thank you! I am working on adding complexity to the Yvonne's working example; our next hurdle is getting a date from the ORACLE database.

I have the following code(myQuery1e)in the .mdq file
select case_num, program_cd, to_char(create_dt,'MM/DD/YYYY HH24:MI:SS') as create_dt from #TEST_VAR#

The rule in the driver map (test_driver.mmc) for the override is:
=VALID(RUN("test_run.mmc"," -ID1 '-MDQ S0890.mdq -DBNAME dev3 -QUERY myQuery1e -VAR TEST_VAR="+In1Test+"' -OF1 test_var_out.txt"),FAIL(LASTERRORMSG() + LASTERRORCODE()))

This does not work.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
test_driver.mmc
<MapAudit StartTime="17:18:45 April 10, 2007">
<Platform> Command Server for Windows - Version 8.1(114)</Platform>
<ExecutionSummary MapStatus="Error" mapreturn="30" ElapsedSec="1.8260" BurstRestartCount="0">
<Message>FAIL function aborted map:One or more inputs was invalid8</Message>
<CommandLine>'C:\WTx\workfolder\Examples\database example\Becky\test_driver.mmc'</CommandLine>
<ObjectsFound>1</ObjectsFound>
<ObjectsBuilt>0</ObjectsBuilt>
<SourceReport card="1" adapter="File" bytes="24" adapterreturn="0">
<Message>Data read successfully</Message>
<Settings>C:\WTx\workfolder\Examples\database example\Becky\db_var.txt</Settings>
<TimeStamp>17:18:31 April 10, 2007</TimeStamp>
</SourceReport>
<TargetReport card="1" adapter="Sink" bytes="0" adapterreturn="0">
<Message>Success</Message>
<TimeStamp>17:18:47 April 10, 2007</TimeStamp>
</TargetReport>
<WorkArea type="File">
<inputarea card="1" Path="C:\WTx\workfolder\Examples\database example\Becky\test_driver.I01" TimeStamp="17:18:47 April 10, 2007" bytes="65605"/>
</WorkArea>
</ExecutionSummary>
</MapAudit>
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
test_run.mmc

<<MapAudit StartTime="17:18:45 April 10, 2007">
<Platform> RUN Function for Windows - Version 8.1(114)</Platform>
<ExecutionSummary MapStatus="Error" mapreturn="8" ElapsedSec="1.8130" BurstRestartCount="0">
<Message>One or more inputs was invalid</Message>
<CommandLine>'C:\WTx\workfolder\Examples\database example\Becky\test_run.mmc' -ID1 '-MDQ S0890.mdq -DBNAME dev3 -QUERY myQuery1e -VAR TEST_VAR=ED_ELIGIBILITY
' -OF1 test_var_out.txt</CommandLine>
<ObjectsFound>1</ObjectsFound>
<ObjectsBuilt>0</ObjectsBuilt>
<SourceReport card="1" adapter="DB" bytes="2464127" adapterreturn="0">
<Message>Success</Message>
<Settings>-MDQ S0890.mdq -DBNAME dev3 -QUERY myQuery1e -VAR TEST_VAR=ED_ELIGIBILITY
</Settings>
<TimeStamp>17:18:45 April 10, 2007</TimeStamp>
</SourceReport>
<WorkArea type="File">
<inputarea card="1" Path="C:\WTx\workfolder\Examples\database example\Becky\test_run.I01" TimeStamp="17:18:47 April 10, 2007" bytes="4587833"/>
</WorkArea>
</ExecutionSummary>
</MapAudit>
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

What do we need to get the date from the ORACLE database? The query works as a standalone. Also, is a type tree needed for the query?

Any help will be appreciated. Thank you.
Becky

#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

The RUN map should have the input tree match the data in the database. So you would need to create a tree from your working query in the MDQ file. However, I suspect you have done something close to this since the test map at some point worked?

I question your select statement - select case_num, program_cd, to_char(create_dt,'MM/DD/YYYY HH24:MI:SS') as create_dt from #TEST_VAR#.

You should not need to do a to_char on the select statement. The tree created from the table or select statement will automatically create the appropriate element to receive a date from Oracle. After you get the data in through the input card, you can then convert it to whatever format (as text or date object) you want using the functions WTX provides.

That being said you should be able to see exactly where the engine is having trouble with the input by turning on the input trace on the RUN map. You can also turn backup on the RUN map input card to make sure that the data you are getting is what you are expecting and matches the tree in the RUN map.

Hope this helps.

Yvonne Ricard
Resistance is NOT futile.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: rarw

Thank you Yvonne for your help.

I was able to get the map working a couple of different ways. The first method read a date from a file, this is similar to your example. The second method used two out put cards and FROMDATETIME(CURRENTDATE(), "dd-mon-ccyy"). This was then used in the query as a variable - passed from out card 1 to out card 2 where the override rule exists.

For me, and the methods that I used, the key points to make it work are:
For the first method - reading the date from a file: the type tree needs to have a field to hold a date as a character string. The rule in the driver map is: =VALID(RUN("test_run.mmc"," -ID1 '-MDQ S0890.mdq -DBNAME dev3 -QUERY myQuery7 -VAR TEST_VAR="In1Test"' -OF1 test_var_out.txt"),FAIL(LASTERRORMSG() + LASTERRORCODE())). "In1Test" maps to the character field in the type tree for the date in the file. The syntax for the query must have single quotes around the variable name: SELECT DISTINCT CASE_NUM
FROM CASE_TABLE A
WHERE A.PROGRAM_CD = 'XX'
AND A.TYPE_OF_ASSISTANCE = 'XX01'
AND A.BEG_DT <= WTX.fday_ofmonth(add_months('#TEST_VAR#', -1))
AND A.END_DT >= LAST_DAY(add_months('#TEST_VAR#', -1))
AND WTX.fCsesReferralStatus(A.CASE_NUM) = 'Y'

Method two uses two output cards and no input card - just using the current date.

I am sure there are better ways to handle dates in WTx, this is most likely just a novice approach!

With your help, we were able to get the appropriate output. Thanks!
Becky
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender