Hi,

I have to create 2 files, one (file-a) must have a decimal point, the other
(file-b) a decimal comma.

Using the SQL Editor I wrote
--------------------------------------------------------
unload to 't:\file-a.txt' DELIMITER ' '
select t_item, t_dsca, t_amount from inv;

unload to 't:\file-b.txt' DELIMITER ' '
select t_item, t_dsca, t_amount from inv;
--------------------------------------------------------

setnet32 is used to set DBMONEY=,

Both files now have a , as decimal sign in t_amount.
How can I temporarily change the , to . ?

TIA,

D. Willems

Hi :-)
I dont know if I understand correctly but you can always either
set the registry value or the environment variable DBMONEY
to the desired value between the two runs via a program.
Environment variable is probably better because it changes
the value only for the current shell.

Regards,
Dirk

--
-- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
-- Steinfurter Str. 61 fax: +49 (0)251 1624111
-- D-48149 Muenster http://www.GunCon.de/
-- "The cost of living hasn't affected its popularity."


"DeeWee" <1@2.3> schrieb im Newsbeitrag news:3f656ca9$0$24143$ba620e4c@reader1.news.skynet .be...
> Hi,
>
> I have to create 2 files, one (file-a) must have a decimal point, the other
> (file-b) a decimal comma.
>
> Using the SQL Editor I wrote
> --------------------------------------------------------
> unload to 't:\file-a.txt' DELIMITER ' '
> select t_item, t_dsca, t_amount from inv;
>
> unload to 't:\file-b.txt' DELIMITER ' '
> select t_item, t_dsca, t_amount from inv;
> --------------------------------------------------------
>
> setnet32 is used to set DBMONEY=,
>
> Both files now have a , as decimal sign in t_amount.
> How can I temporarily change the , to . ?
>
> TIA,
>
> D. Willems
>
>

Thanks for your reaction.
Your suggestion is correct.
I hoped that I could do it in the SQL script i.e. between the two unload's
without leaving the script.

Hoping you have such solution,

D. Willems


"Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
news:bk6u46$pdh$1@news1.transmedia.de...
> Hi :-)
> I dont know if I understand correctly but you can always either
> set the registry value or the environment variable DBMONEY
> to the desired value between the two runs via a program.
> Environment variable is probably better because it changes
> the value only for the current shell.
>
> Regards,
> Dirk
>
> --
> -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
> -- Steinfurter Str. 61 fax: +49 (0)251 1624111
> -- D-48149 Muenster http://www.GunCon.de/
> -- "The cost of living hasn't affected its popularity."
>
>
> "DeeWee" <1@2.3> schrieb im Newsbeitrag
news:3f656ca9$0$24143$ba620e4c@reader1.news.skynet .be...
> > Hi,
> >
> > I have to create 2 files, one (file-a) must have a decimal point, the
other
> > (file-b) a decimal comma.
> >
> > Using the SQL Editor I wrote
> > --------------------------------------------------------
> > unload to 't:\file-a.txt' DELIMITER ' '
> > select t_item, t_dsca, t_amount from inv;
> >
> > unload to 't:\file-b.txt' DELIMITER ' '
> > select t_item, t_dsca, t_amount from inv;
> > --------------------------------------------------------
> >
> > setnet32 is used to set DBMONEY=,
> >
> > Both files now have a , as decimal sign in t_amount.
> > How can I temporarily change the , to . ?
> >
> > TIA,
> >
> > D. Willems
> >
> >
>
>

Hi again :-)

I cant see why dividing your dbaccess script into two parts
would be harmfull.

Nevertheless if you really have to do it in one script I would
suggest to set DBMONEY to . and write a Stored
Procedure which changes . to , and use that on the
appropriate columns during the second unload.

Hope that helps.

Regards,
Dirk


> Thanks for your reaction.
> Your suggestion is correct.
> I hoped that I could do it in the SQL script i.e. between the two unload's
> without leaving the script.
>
> Hoping you have such solution,
>
> D. Willems
>
>
> "Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
> news:bk6u46$pdh$1@news1.transmedia.de...
> > Hi :-)
> > I dont know if I understand correctly but you can always either
> > set the registry value or the environment variable DBMONEY
> > to the desired value between the two runs via a program.
> > Environment variable is probably better because it changes
> > the value only for the current shell.
> >
> > Regards,
> > Dirk
> >
> > --
> > -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
> > -- Steinfurter Str. 61 fax: +49 (0)251 1624111
> > -- D-48149 Muenster http://www.GunCon.de/
> > -- "The cost of living hasn't affected its popularity."
> >
> >
> > "DeeWee" <1@2.3> schrieb im Newsbeitrag
> news:3f656ca9$0$24143$ba620e4c@reader1.news.skynet .be...
> > > Hi,
> > >
> > > I have to create 2 files, one (file-a) must have a decimal point, the
> other
> > > (file-b) a decimal comma.
> > >
> > > Using the SQL Editor I wrote
> > > --------------------------------------------------------
> > > unload to 't:\file-a.txt' DELIMITER ' '
> > > select t_item, t_dsca, t_amount from inv;
> > >
> > > unload to 't:\file-b.txt' DELIMITER ' '
> > > select t_item, t_dsca, t_amount from inv;
> > > --------------------------------------------------------
> > >
> > > setnet32 is used to set DBMONEY=,
> > >
> > > Both files now have a , as decimal sign in t_amount.
> > > How can I temporarily change the , to . ?
> > >
> > > TIA,
> > >
> > > D. Willems
> > >
> > >
> >
> >
>
>

Thanks for your advices.

Since I've never written a stored procedure I'll split the script.

Thanks

"Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
news:bkekch$h12$1@news1.transmedia.de...
> Hi again :-)
>
> I cant see why dividing your dbaccess script into two parts
> would be harmfull.
>
> Nevertheless if you really have to do it in one script I would
> suggest to set DBMONEY to . and write a Stored
> Procedure which changes . to , and use that on the
> appropriate columns during the second unload.
>
> Hope that helps.
>
> Regards,
> Dirk
>
>
> > Thanks for your reaction.
> > Your suggestion is correct.
> > I hoped that I could do it in the SQL script i.e. between the two
unload's
> > without leaving the script.
> >
> > Hoping you have such solution,
> >
> > D. Willems
> >
> >
> > "Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
> > news:bk6u46$pdh$1@news1.transmedia.de...
> > > Hi :-)
> > > I dont know if I understand correctly but you can always either
> > > set the registry value or the environment variable DBMONEY
> > > to the desired value between the two runs via a program.
> > > Environment variable is probably better because it changes
> > > the value only for the current shell.
> > >
> > > Regards,
> > > Dirk
> > >
> > > --
> > > -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
> > > -- Steinfurter Str. 61 fax: +49 (0)251 1624111
> > > -- D-48149 Muenster http://www.GunCon.de/
> > > -- "The cost of living hasn't affected its popularity."
> > >
> > >
> > > "DeeWee" <1@2.3> schrieb im Newsbeitrag
> > news:3f656ca9$0$24143$ba620e4c@reader1.news.skynet .be...
> > > > Hi,
> > > >
> > > > I have to create 2 files, one (file-a) must have a decimal point,
the
> > other
> > > > (file-b) a decimal comma.
> > > >
> > > > Using the SQL Editor I wrote
> > > > --------------------------------------------------------
> > > > unload to 't:\file-a.txt' DELIMITER ' '
> > > > select t_item, t_dsca, t_amount from inv;
> > > >
> > > > unload to 't:\file-b.txt' DELIMITER ' '
> > > > select t_item, t_dsca, t_amount from inv;
> > > > --------------------------------------------------------
> > > >
> > > > setnet32 is used to set DBMONEY=,
> > > >
> > > > Both files now have a , as decimal sign in t_amount.
> > > > How can I temporarily change the , to . ?
> > > >
> > > > TIA,
> > > >
> > > > D. Willems
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Ok. But stored procedures are your friends really :-)
Maybe you want to try the following easy stored proc:

CREATE PROCEDURE dot2comma(theinput CHAR(255)) RETURNING CHAR(255);
DEFINE theoutput CHAR(255);
DEFINE i SMALLINT;
LET i=1;
LET theoutput=theinput;
WHILE i<=LENGTH(theoutput)
IF SUBSTRING(theoutput FROM i FOR 1)="." THEN
LET theoutput=SUBSTRING(theoutput FROM 1 FOR i-1)
||","
||SUBSTRING(theoutput FROM i+1 FOR LENGTH(theoutput)-i);
END IF;
LET i=i+1;
END WHILE;
RETURN theoutput;
END PROCEDURE

The procedure knows nothing about number formats. It just changes all
dots in a string to commas.

Your second unload would then change to:

unload to 't:\file-b.txt' DELIMITER ' '
select t_item, t_dsca, dot2comma(t_amount) from inv;

Regards,
Dirk Gunsthoevel

--
-- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
-- Steinfurter Str. 61 fax: +49 (0)251 1624111
-- D-48149 Muenster http://www.GunCon.de/
-- "The cost of living hasn't affected its popularity."


"DeeWee" <1@2.3> schrieb im Newsbeitrag news:3f6e9ade$0$31715$ba620e4c@reader1.news.skynet .be...
> Thanks for your advices.
>
> Since I've never written a stored procedure I'll split the script.
>
> Thanks
>
> "Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
> news:bkekch$h12$1@news1.transmedia.de...
> > Hi again :-)
> >
> > I cant see why dividing your dbaccess script into two parts
> > would be harmfull.
> >
> > Nevertheless if you really have to do it in one script I would
> > suggest to set DBMONEY to . and write a Stored
> > Procedure which changes . to , and use that on the
> > appropriate columns during the second unload.
> >
> > Hope that helps.
> >
> > Regards,
> > Dirk
> >
> >
> > > Thanks for your reaction.
> > > Your suggestion is correct.
> > > I hoped that I could do it in the SQL script i.e. between the two
> unload's
> > > without leaving the script.
> > >
> > > Hoping you have such solution,
> > >
> > > D. Willems
> > >
> > >
> > > "Dirk Gunsthövel" <dirk@guncon.de> schreef in bericht
> > > news:bk6u46$pdh$1@news1.transmedia.de...
> > > > Hi :-)
> > > > I dont know if I understand correctly but you can always either
> > > > set the registry value or the environment variable DBMONEY
> > > > to the desired value between the two runs via a program.
> > > > Environment variable is probably better because it changes
> > > > the value only for the current shell.
> > > >
> > > > Regards,
> > > > Dirk
> > > >
> > > > --
> > > > -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 624947
> > > > -- Steinfurter Str. 61 fax: +49 (0)251 1624111
> > > > -- D-48149 Muenster http://www.GunCon.de/
> > > > -- "The cost of living hasn't affected its popularity."
> > > >
> > > >
> > > > "DeeWee" <1@2.3> schrieb im Newsbeitrag
> > > news:3f656ca9$0$24143$ba620e4c@reader1.news.skynet .be...
> > > > > Hi,
> > > > >
> > > > > I have to create 2 files, one (file-a) must have a decimal point,
> the
> > > other
> > > > > (file-b) a decimal comma.
> > > > >
> > > > > Using the SQL Editor I wrote
> > > > > --------------------------------------------------------
> > > > > unload to 't:\file-a.txt' DELIMITER ' '
> > > > > select t_item, t_dsca, t_amount from inv;
> > > > >
> > > > > unload to 't:\file-b.txt' DELIMITER ' '
> > > > > select t_item, t_dsca, t_amount from inv;
> > > > > --------------------------------------------------------
> > > > >
> > > > > setnet32 is used to set DBMONEY=,
> > > > >
> > > > > Both files now have a , as decimal sign in t_amount.
> > > > > How can I temporarily change the , to . ?
> > > > >
> > > > > TIA,
> > > > >
> > > > > D. Willems
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>