Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Due to some maintenance operations, stambia.org will be switched to read-only mode during the 13th November. It will be possible to read and download, but impossible to post on the forums or create new accounts. For any question please contact the support team.

Topic-icon Question Use a parameter in a mapping

More
28 Mar 2016 16:45 #1 by Afaf ABOUTABIT
Afaf ABOUTABIT created the topic: Use a parameter in a mapping
Hello Everyone,

I am using a File Wait component to get some files and I execute a mapping on each file.
In the mapping, one field has to be filled with the file name that is beeing treated.
Is there any way to read the value of the file name and use it in the mapping ?

Thank you :)
More
29 Mar 2016 12:10 #2 by Thomas BLETON
Thomas BLETON replied the topic: Use a parameter in a mapping
Hi,
the next version of Stambia will have a new "Property fields" feature which will help a lot for this (Designer S18.1.1 and Runtime 17.2.16 if I remember well).

For now, as this feature is not released yet, here is how you can do:
- after the "File Wait" action, place an Execute Delivery with a Bind link.
- add a "FileName" parameter on this Execute Delivery, with the value :{FILE_NAME}:
- in your mapping expression, use this syntax: ${~/FileName}$ to reference the passed parameter.
More
04 Apr 2016 10:13 #3 by Afaf ABOUTABIT
Afaf ABOUTABIT replied the topic: Use a parameter in a mapping
Hi Thomas,

I executed what you suggested then I got this error while the insertion of pre-integrated data step :
"Entrée manquante de la clause FROM pour la table nom_fichier"

Could you help me figure out what causes this error ?

Thank you :)
More
04 Apr 2016 12:12 #4 by Thomas BLETON
Thomas BLETON replied the topic: Use a parameter in a mapping
What is the SQL query generated for this step (Step details view) ?
More
04 Apr 2016 12:39 - 04 Apr 2016 12:40 #5 by Afaf ABOUTABIT
Afaf ABOUTABIT replied the topic: Use a parameter in a mapping
Here it is
insert into "DB".edi.I_PICKSLIP_non_MAJ
(
    date,
    "PICKSLIP_non_MAJ_NUM",
    nom_fichier,
    pick_slip,
    INCREMENTAL_FLAG
)
select
    CURRENT_TIMESTAMP AS date,
    nextval('edi."PICKSLIP_non_MAJ_PICKSLIP_non_MAJ8NUM_seq"') AS "PICKSLIP_non_MAJ_NUM",
    ${~/p_File_Name}$ AS nom_fichier,
    expedition.c3
 AS pick_slip,
    'I' AS INCREMENTAL_FLAG
from    "DB".edi.expedition AS expedition
    LEFT OUTER JOIN "DB".edi.L1_PICKSLIP_non_MAJ AS L1_PICKSLIP_non_MAJ
    ON expedition.c3 = L1_PICKSLIP_non_MAJ.L1_pick_slip

where    (1=1) AND  ( expedition.c3 not in (select pick_slip from work.eb_chargement) )
Last Edit: 04 Apr 2016 12:40 by Afaf ABOUTABIT.
More
05 Apr 2016 09:36 #6 by Thomas BLETON
Thomas BLETON replied the topic: Use a parameter in a mapping
Usually, when a SQL query causes an error, we try to execute it from a Querying tool (Squirrel, or a Designer's SQL Editor window). This helps understanding what causes the error, and helps to see what would be the correct query. Can you try this ?
Once you understand the cause of the error, you can modify your mapping to make it work - and report an issue if you feel there is something wrong in the SQL query generation.
Let me know if you need further help.
More
09 Jun 2016 15:00 #7 by Prashant.Sangle
Prashant.Sangle replied the topic: Use a parameter in a mapping
Hi Sir,

I am trying the same in my mapping getting below error.

Load of data (TRG)
java.sql.SQLException: java.io.FileNotFoundException: D:\devl\stambia\TestFiles\reject_Test\${~\p_FILE_NAME}$ (The system cannot find the path specified)

Please guide me to resolve it.

Thanks and Regards,
Prashant
More
09 Jun 2016 17:53 #8 by Thomas BLETON
Thomas BLETON replied the topic: Use a parameter in a mapping
Hi Prashant,

It looks like the parameter is not resolved. How is it initialized ?
Can you provide more details on what you need to do and how you tried do it ?
More
10 Jun 2016 07:47 #9 by Prashant.Sangle
Prashant.Sangle replied the topic: Use a parameter in a mapping
HI,

I have 3 delimated file with same structure say a.txt,b.txt and c.txt.
I want to push data of that 3 files into single target table say trg_sql_table
Now I have created simple mapping which load a.txt to trg_sql_table.

To create dynamic structure I tried below.

1: create process with File wait action and properties set
Wait File Dir :
Wait File Includes :
and drag and drop existing file source metadata.

2: Then use ExecuteDelivery action with properties set
add a "p_FILE_NAME" parameter on this Execute Delivery, with the value :{FILE_NAME}:
add deliv_NAME which created in simple mapping.

3: then change flat file metadata properties
Physical Name : ${~/p_FILE_NAME}$

Then first execute process after that tried to execute mapping then I am getting error

Is this correct way to achieve it or need different logic

Thanks and Regards,
Prashant
More
10 Jun 2016 11:42 #10 by Thomas BLETON
Thomas BLETON replied the topic: Use a parameter in a mapping
Hi,
You wrote the error message is :
Load of data (TRG)
java.sql.SQLException: java.io.FileNotFoundException: D:\devl\stambia\TestFiles\reject_Test\${~\p_FILE_NAME}$ (The system cannot find the path specified)

=> The parameter name contains a backslash \ which is not correct.
=> Please check that the file metadata is using a slash, not a backslash.