Thread: View - Join based on dis-similar data types

I have a field defined as a character(50) data type, and the same field
stored in a transition table as a text type.

The view itself work based on the join, however sub-queries against the view
on that field do not return any data.

Example:

Table1
----
T1Data - Character(50)


Table2
----
T2Data - Text


View1:
---
SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data);


A select against View1
SELECT * FROM View1 returns all relevant records, however, adding a WHERE
clause produces no output.

Please advise.

"speakeasy" <nospam_artd@speakeasy.net> writes:
> I have a field defined as a character(50) data type, and the same field
> stored in a transition table as a text type.

> The view itself work based on the join, however sub-queries against the view
> on that field do not return any data.

You probably need to rtrim() the CHAR field so that the trailing blanks
in it don't foul up the comparison to the TEXT field.  Trailing blanks
are considered insignificant in CHAR comparisons ... but not in TEXT
comparisons.  So,
WHERE rtrim(charfield) = textfield

should work.
        regards, tom lane

Not sure if this ever made it to the group, I can't seem to find it in the
recent message lists.

"speakeasy" <nospam_artd@speakeasy.net> wrote in message news:...
> I have a field defined as a character(50) data type, and the same field
> stored in a transition table as a text type.
>
> The view itself work based on the join, however sub-queries against the
view
> on that field do not return any data.
>
> Example:
>
> Table1
> ----
> T1Data - Character(50)
>
>
> Table2
> ----
> T2Data - Text
>
>
> View1:
> ---
> SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data);
>
>
> A select against View1
> SELECT * FROM View1 returns all relevant records, however, adding a WHERE
> clause produces no output.
>
> Please advise.
>
>

On Thursday 13 Mar 2003 1:48 pm, speakeasy wrote:
> Not sure if this ever made it to the group, I can't seem to find it in the
> recent message lists.
>
> "speakeasy" <nospam_artd@speakeasy.net> wrote in message news:...
>
> > I have a field defined as a character(50) data type, and the same field
> > stored in a transition table as a text type.
> >
> > The view itself work based on the join, however sub-queries against the
> view
> > on that field do not return any data.
> >
> > Example:
> >
> > Table1
> > T1Data - Character(50)

> > Table2
> > T2Data - Text

> > View1:
> > ---
> > SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data);
> >
> >
> > A select against View1
> > SELECT * FROM View1 returns all relevant records, however, adding a WHERE
> > clause produces no output.

Can you please provide a real example, otherwise we're just guessing.
Are you sure you've not forgotten about the spaces padding your char(50)
field?

--  Richard Huxton