Hi Everyone

I just discovered a strange problem and it is really bugging me, since I fail to understand what could cause this.

I am concatenating onto a field and sometimes it leaves a space after the field and sometimes it does not.

Here is an example.

DEFINE FILE SOMETABLE
THEJOIN/A22= SERV | 'A';
WHATEVER/A1='X';
END
-*-
TABLE FILE SOMETABLE
SUM
SOMEMEASURE
BY THEJOIN
BY WHATEVER
END

This keeps a space. THEJOIN looks like this "VALUE A"

However if I remove WHATEVER from displaying... like this...

DEFINE FILE SOMETABLE
THEJOIN/A22= SERV | 'A';
END
-*-
TABLE FILE SOMETABLE
SUM
SOMEMEASURE
BY THEJOIN
END

Then I am getting no space in THEJOIN. It looks like this "VALUEA".

So the final data is exactly the same in both cases except there is a space in the first example and no space in the second example.

This is causing me all kinds of trouble since the space is erratically showing up and I use that field in a join later on.

Of course I can get around the problemn with a strong concat everywhere.

But I want to understand why this is happening. Is it a bug? Any ideas?

Thanks

Jodye

PS this forum really needs a "preview post" before it is posted.

DEFINE FILE CAR
THEJOIN/A22= COUNTRY | 'A';
WHATEVER/A1='X';
END
-*-
TABLE FILE CAR
SUM
SEATS
BY THEJOIN
BY WHATEVER
END

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

DEFINE FILE CAR
THEJOIN/A22= COUNTRY | 'A';
END
-*-
TABLE FILE CAR
SUM
SEATS
BY THEJOIN
END

The space stays when I use the CAR file, with or without BY WHATEVER.

Is the field a variable length field?

quote:

THEJOIN/A22= SERV | 'A';
WHATEVER/A1='X';

Try using the Strong '||' concatenation if it is an issue with a space after what is in SERV.

What's the Format of SERV?

Hi

Francis: Yes I tried to replicate the problem with CAR and I don't see it either. The field is A20.

Leah: I know that strong concat will fix the problem. I simply want to understand what is going on. Why is there sometimes a space for the exact same data? Why should adding a defined field change the behaviour of a totally unrelated field?

Thanks!

Jodye

Hi JG

It is A20. If I view source I see that the value is either

"value               a"

or "valuea"

depending on whether or not the additional BY is there. So the weak concatenation is not working in one case.

thx

Try this:

DEFINE FILE SOMETABLE
THEJOIN/A22= SERV || (' ' | 'A');
END

jodye,

If SERV is A20 and THEJOIN is A22, try

THEJOIN/A22= SERV | ' A';

Jodye,

I take it that the output is HTML? I would trap the SET values and also check out the source to see if there's a font issue? Can't see that being the case myself but ....

T

This message has been edited. Last edited by: Tony A,

The code you posted is obviously an example.
Maybe it helps if you provide us with the 'real' code that is giving you headaches. Since you want to understand what is happening (alongside solving your problem) I think we can do that best with your real code.

Jodye

Is your data source - SOMETABLE - coming from a relational DB? If so, which one?

EricH

Is the format of the SERV field A20 and not A20V - just wanted to check because this WOULD cause the problem you are seeing. The extra V is due to a VARCHAR field in the DB and WF translates it to a variable length Alpha field in which case, there are no trailing spaces.

Hi Everyone

First of all, you guys rock. This forum is such an excellent resource. I wish I was going to the conference this year to meet you all. Maybe next year.

Ok here is some clarification. I am not really concatenating the letter "A". What I am really doing is extracting data from two different sources and then joining the hold files. The problem I found yesterday is that sometimes the join works and sometimes it does not. And after some debugging, I found the problem.

This will demonstrate.

PLEASE NOTE: In the following, FIELD1 and FIELD2 in the defines for THEJOIN are exatcly the same in both extracts. They come from SOURCE3. Also, this is sample code. Please assume that I have a good reason for extracting the data separately and then joining the hold files.

-*************
-*extract 1
-*************
JOIN CLEAR *
JOIN ID IN SOURCE1 TO ID IN SOURCE3 AS J1 END
-RUN

DEFINE FILE SOURCE1
THEJOIN/A100=FIELD1 | FIELD2; 
END

TABLE FILE SOURCE1
SUM
X
Y
BY THEJOIN
ON TABLE HOLD AS H1 FORMAT FOCUS INDEX THEJOIN
END

-*************
-*extract 2
-*************
JOIN CLEAR *
JOIN ID IN SOURCE2 TO ID IN SOURCE3 AS J1 END
-RUN

DEFINE FILE SOURCE2
THEJOIN/A100=FIELD1 | FIELD2;
END
TABLE FILE SOURCE2
SUM
Z
BY THEJOIN
ON TABLE HOLD AS H2 FORMAT FOCUS INDEX THEJOIN
END

Then once I have the data I join the hold files using THEJOIN.

Now, getting back to the problem at hand.... I am getting an extra space some of the time.
So.. like I said... here is what is happening. In the following, I changed FIELD2 to "A" because it makes the problem more clear.
FIELD1 is A20 and NOT A20V. A typical value for FIELD1 is "CANADA".

So once again... here is an illustration... using only one table, since the problem happens with either table.

-****************************
this code gives me THEJOIN="CANADAA"

DEFINE FILE SOURCE2
THEJOIN/A21=FIELD1 | 'A';
W_AVAIL/P12=IF W_AVAIL LT 0 THEN 0 ELSE W_AVAIL;
END
TABLE FILE SOURCE2
SUM
Z
W_AVAIL
BY THEJOIN
ON TABLE HOLD AS H2 FORMAT FOCUS INDEX THEJOIN
END
-****************************
this code gives me THEJOIN="CANADA A"

DEFINE FILE SOURCE2
THEJOIN/A21=FIELD1 | 'A';
W_AVAIL/P12=IF W_AVAIL LT 0 THEN 0 ELSE W_AVAIL;
END
TABLE FILE SOURCE2
SUM
Z
-*commented out W_AVAIL
BY THEJOIN
ON TABLE HOLD AS H2 FORMAT FOCUS INDEX THEJOIN
END
-****************************

As you can see the only difference is that I commented out the SUM OF W_AVAIL in the second example. The problem occurs in excel as well as HTML.

Now I feel like I have opened a can of worms. Please note, I am NOT asking how to fix the problem. I can fix it in 2 seconds by using strong concatenation. Rather, I am asking WHY is the extra space sometimes there and sometimes not. That is all I need to know. I figure that there must be an explanation for this and that I should know what it is. It is driving me crazy actually.

Unfortunately I cannot replicate the problem using CAR or any other sample. My source is TeraData by the way.

Thanks again.

Is there a posibility, the data is bad?

Have you looked at the values in Field1 ?

You can use UFMT to show the HEX version of the data.

Hex/A40 = UFMT(Field1,20, Hex);

check to see if the tail of the data is all 20's

quote:

Unfortunately I cannot replicate the problem using CAR or any other sample. My source is TeraData by the way

That's a toughie. Can you duplicate this using another field or table in Teradata? The fact that you can't reproduce using other data sources would lead me to believe that it is related to Teradata and/or the Teradata adapter, both of which I unfortunately have never used.

There are a few settings like "Transaction Mode" that are unique to each data adapter. You might want to have a look at the Adapter Administration manual Chapter on the Teradata Adapter and see if anything rings a bell.

Any other Teradata users out there?

We use Teradata a lot here. However, noone has complained. I'd actually take a look at Waz' suggestion and see if you have a non-printable character in some of the data fields. At this point, it sounds like the only plausible reason for this to be happening.

Hi Again

Turns out you guys are right. It is VARCHAR in the table. The person who made tha master file set the field manually to A20 but it is really A20V in the DB.

So why does that cause the extra space to SOMETIMES show? Why doesn't it always show? What factors determine whether the trailing spaces are retained or not?

Thanks so much

Jodye

hi jodye
in my experience with /AnV fields in some rdbms, (after alot of ageda,) i found that
1) i couldn't just edit the master to change /AnV to /An
2) i couldn't concat, as you found
3) i could redefine the original field, alone, and then the concat worked fine.
DEFINE FILE SOMEFILE
THEJOIN/A20=SERV;
THENEWJOIN/A22=THEJOIN | ' A';
END
What's worse...if the developers in the group want to extract data directly from SOMEFILE,
and they say TABLE FILE SOMEFILE
PRINT .. SERV ...
ON TABLE HOLD AS MYFILE
END
...the data in the .ftm flat file is a screaming MESS because of that /AnV field. (try it, you'll see why you're getting the results that you are). So we had to put a define in the master, and build a Business View master to reveal to the developers. Worked!
Sorry we won't see you at Summit..
-S.

What are your settings for the Teradata connection.

Have you used

ENGINE [SQLDBC] SET VARCHAR OFF

This may fix your problem.

Hi Susannah and Waz

You know what, that is why we started changing A20V to A20 in the master file. Because we were holding data and reading it into ampers and it was totally screwed up. We never noticed before that the nV was causing weird problems with weak concatenation. now that we know we will deal with it accordingly.

Waz: I tried putting
ENGINE SQLDBC SET VARCHAR OFF
in the fex and it did not help. Maybe it has to be in the server configuration file?

I still don't get why the varchar acts so erratically, but it is enough to know that I will expect that from now on and deal with it.

Thanks everyone.

Jodye,

You have to regen the masters for the VARCHAR OFF setting to have any effect. See current post by Francis M. on the same topic for SQLMSS. The answers apply to you as well.

quote:

Originally posted by jodye:
Hi Susannah and Waz

You know what, that is why we started changing A20V to A20 in the master file. Because we were holding data and reading it into ampers and it was totally screwed up. We never noticed before that the nV was causing weird problems with weak concatenation. now that we know we will deal with it accordingly.

Waz: I tried putting
ENGINE SQLDBC SET VARCHAR OFF
in the fex and it did not help. Maybe it has to be in the server configuration file?

I still don't get why the varchar acts so erratically, but it is enough to know that I will expect that from now on and deal with it.

Thanks everyone.

One thing that we have done in conjunction with the "ENGINE SQLDB2 SET VARCHAR OFF" is using the line "ENGINE SQLDB2 SET DEFAULT_CONNECTION [ADAPTER NAME] which may help point to the correct data source if that is the issue.

Another thing you could try when using a -READ is formating the SQL passthrough output to an alpha format, then reading a 6 character length variable, and then the Alpha. For example, if you have a varchar(20) that you want to read:

--------------------------------------------
-DEFAULT &VARLENGTH1 = '[6 SPACES]', &VARIABLE = '[20 SPACES]';
ENGINE SQLDB2
SQL SQLDB2
SELECT VARIABLE
FROM TABLE;
TABLE FILE SQLOUT
PRINT VARIABLE
ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA
END
-RUN
-READ HOLDTEMP &VARLENGTH1.A6., &VARIABLE.A20.
-TYPE &VARLENGTH1;
-TYPE &VARIABLE;

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


Reading the 6 character length value in the &VARLENGTH1 before reading the &VARIABLE has worked for us. It also allows the use of the variable's length if needed later on.


I hope this helps,

-Jock