Hi Jasmine,
The first solution would be to create a Location code id that would be for instance:
It should be quite easy if you can create new columns in both your tables.
The other solution would be to create a technical column that would have the following expression in both your tables (in datamodule or Framework Manager):
case length([Location Code])
when 4 then '++' + [Location Code]
when 5 then '+' + [Location Code]
else [Location Code]
end
The result is the following:
Then, you can create a join on the Location ID column.
Best regards,
------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------
Original Message:
Sent: Thu April 14, 2022 03:59 PM
From: Jasmine D
Subject: Varchar join not working with different character lengths
I have two queries I need to join on location code. Location code is an alphanumeric field between 4 and 6 characters.
In query one I'm using a case statement to bring in the location code so I'm having a problem with incompatible data types for the join.
In both queries i'm doing: cast ( [Location Code] , varchar(6) )
The join will match on my location codes with 6 characters but not for location codes with 4 or 5 characters.
Any advice on how to fix this?
------------------------------
Jasmine D
------------------------------
#CognosAnalyticswithWatson