How to read from a parameter file having multilines.

[#1]

Hi,

I would like to accomplish the following scenario in QV.
I have a parameter file: parameter.txt. In this file, I have lines:
POS_1='AXP','ORB','CTIX','PTM','RQST','PLQL','MAR','CTP','FRN','TVE','MTE','JTGA','MOT'

POS_2='HCL','HCRG','CAN','ESR','HAA','EBCH','EBDE'

POS_3='MJDK','MJSE','NATW','COUT','EBAT','EBBE'

Now, I need to read the values assigned to each of the POS_* in my QV script and use them in a Query. For example:

-------------------------------

Temp_Table:
select POS_Name, POS_Location
from
test.pos_history
where pos_id in (Read value of POS_1 from parameter.txt)    ////Need your help here

concatenate
select

POS_Name, POS_Location

from

test.pos_history

where pos_id in (Read value of POS_2 from parameter.txt)    ////Need your help here

;

--------------------------------

Thank you.

[alexandros17]

Here is your code for a single file

AAA:

load * inline [

pos_name

"POS_1='AXP','ORB','CTIX','PTM','RQST','PLQL','MAR','CTP','FRN','TVE','MTE','JTGA','MOT'"

"POS_2='HCL','HCRG','CAN','ESR','HAA','EBCH','EBDE'"

"POS_3='MJDK','MJSE','NATW','COUT','EBAT','EBBE'"

];

BBB:

NoConcatenate

LOAD Subfield(Mid(pos_name, Index(pos_name,'=')+1),',') as str Resident AAA;

DROP Table AAA;

[#3]

Hi. Thanks for your reply.

But it seems, I should not use Inline Table in my script. The reason for this is not to touch the code in future, whenever I need to add/delete a POS id. Only place to change is the parameter file.

The second point of using Concatenate in my script is, data for each POS is so huge that if I keep all of them together, it results in DB time out error. Hence, I decided to divide it multiple SQL and merge them using Concatenate.

[alexandros17]

I used the inline only to load data for my example, you may load data from other sources obviously ...

If you use the code I send you, and fix it for your file, then you have only to replicate it and concatenate each other

[maxgro]

see attachment

I started from Alessandro script (tkanks)

parameter.txt is your file

pos.txt is a test file (replace it with pos_history)

SCRIPT

Directory;

AAA:

LOAD @1 as pos_name

FROM

parameter.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

BBB:

load pos, replace(str, chr(39), '') as str;

NoConcatenate

LOAD

mid(pos_name,1,5) as pos,

Subfield(Mid(pos_name, Index(pos_name,'=')+1),',') as str Resident AAA;

DROP Table AAA;

CCC:

left keep (BBB)

LOAD @1 as pos,

     @2 as str

FROM

pos.txt

(txt, codepage is 1252, no labels, delimiter is ',', msq);

DROP Table BBB;

[#6]

Got your point. I tried doing that, but stuck at the second point of my question (if you see my question above...there is a place which I commented 'I need your help here').
At that point, how would I be able to call a QV table inside a SQL query? Means, How could I write something as:

Temp_Table:

select POS_Name, POS_Location

from

test.pos_history

where pos_id in (Load str from BBB)    /// showing error here

////Need your help here: As AAA is a QV table while pos_history is my DB table.

[#7]

Hello Massimo. Thanks for your inputs. But I find myself stuck at a point as how I could use the final values in the SQL statement...means:

Temp_Table:

select POS_Name, POS_Location

from

test.pos_history

where pos_id in (Load str from CCC )    /// showing error here

////Need your help here: As CCCis a QV table while pos_history is my DB table

[maxgro · Accepted solution]

I think you have to loop on your QV table  (in my example parameter.txt)

AAA:

LOAD

  @1 as pos_name,

  Right(@1, Len(@1)-6 ) as field

FROM parameter.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET rows=NoOfRows('AAA');

for i=0 to $(rows)-1

LET filter=peek('field', $(i));

  trace $(i);

  trace $(filter);

  TABLE:

  SQL

  select POS_Name, POS_Location

  from

  test.pos_history

  where pos_id in ('$(filter)');    ////Need your help here

NEXT;

[#9]

Thank you!