Question Use a parameter in a mapping

Afaf ABOUTABIT
Topic Author
Offline

28 Mar 2016 16:45 #1 by Afaf ABOUTABIT

Use a parameter in a mapping was created by Afaf ABOUTABIT

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

Thomas BLETON
Offline

29 Mar 2016 12:10 #2 by Thomas BLETON

Replied by Thomas BLETON on 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.

Afaf ABOUTABIT
Topic Author
Offline

04 Apr 2016 10:13 #3 by Afaf ABOUTABIT

Replied by Afaf ABOUTABIT on 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

Thomas BLETON
Offline

04 Apr 2016 12:12 #4 by Thomas BLETON

Replied by Thomas BLETON on topic Use a parameter in a mapping

What is the SQL query generated for this step (Step details view) ?

Afaf ABOUTABIT
Topic Author
Offline

04 Apr 2016 12:39 - 04 Apr 2016 12:40 #5 by Afaf ABOUTABIT

Replied by Afaf ABOUTABIT on 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.

Thomas BLETON
Offline

05 Apr 2016 09:36 #6 by Thomas BLETON

Replied by Thomas BLETON on 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.

Prashant.Sangle
Offline

09 Jun 2016 15:00 #7 by Prashant.Sangle

Replied by Prashant.Sangle on 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

Thomas BLETON
Offline

09 Jun 2016 17:53 #8 by Thomas BLETON

Replied by Thomas BLETON on 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 ?

Prashant.Sangle
Offline

10 Jun 2016 07:47 #9 by Prashant.Sangle

Replied by Prashant.Sangle on 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

Thomas BLETON
Offline

10 Jun 2016 11:42 #10 by Thomas BLETON

Replied by Thomas BLETON on 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.