• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Sql Query Value formatting join problem

Hi

This one should be simple enough to answer but I can not see the problem..

I try to do an inline select based on a value.

Select b.column1, (select a.description from aTable a where a.column1=b.column1)
from bTable.

My inline query returns a null even though the column1 from both tables looks the same to me in each row.
The b.column1 is defined as nvarchar2(20)
The a.column1 is defined as varchar2(255 bite)

The b.column1 was imported from tabbed delimited text file using ssis.

Is there someone who can advise me on how to proceed?
0
devguru001
Asked:
devguru001
1 Solution
 
Pratima PharandeCommented:
try to use Trim on bothe the fields in comparision
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Have you checked for leading and/or trailing spaces in both columns?!
Maybe you could try to trim both?!?
0
 
devguru001Author Commented:
Hi, I tried the sql trim statement and it did not help.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Geert GruwezOracle dbaCommented:
use an upper trim ?

Select b.column1, (select a.description from aTable a where upper(trim(a.column1)) =upper(trim(b.column1)))
from bTable.
0
 
devguru001Author Commented:
Hi
The value in both columns looks like 'WYB'. When I hard code this value to replace a.column1 I get my desired result. When I use a.Column1 I get nothing.
I am using TOAD for Oracle and can not see a reason, I also thought it should be a trim issue but it is not helping, converting to upper did not help either, upon inspection it was also obvious that the case was not the issue.
0
 
devguru001Author Commented:
Does anyone know of a way that I can analyse each value somehow to see how they differ in formatting etc?
0
 
Geert GruwezOracle dbaCommented:
yeah, use dump to see the characters

Select dump(b.column1) from bTable b

select dump(a.column1) from atable a
0
 
Geert GruwezOracle dbaCommented:
possible you need a to_char on both :

Select b.column1, (select a.description from aTable a where to_char(a.column1)=to_char(b.column1))
from bTable.
0
 
devguru001Author Commented:
Hi Geert,

The dump statement let me see that there were tabs and spaces included in the string of a.column1. If I remove those my query will be solved, as such you win the grand prize :)

Thanks everyone for your time and suggestions.
0
 
devguru001Author Commented:
If anyone is interested, this is the code which removed tabs and spaces etc:

regexp_replace(a.column1, '[[:space:]]',null)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now