I am trying to build a case/if statement in a JOIN ON
clause.
LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = [CTE].[F61]
The problem is that the column [Statuses].[STRID]
contains text and numbers. The column I am comparing it to [CTE].[F61]
is an integer.
Is there a way to detect if column [Statuses].[STRID]
has a character or a number and THEN set it to 0 if it is a character?
Here is a pseudo query to help:
LEFT JOIN [CTSTRC] [Statuses]
ON RIGHT((
CASE [Statuses].[STRID]
WHEN TEXT THEN 0
ELSE CAST([Statuses].[STRID] AS INT) END), 3) = [CTE].[F61]
There are plenty of ways to resolve for this: a subquery with a CASE statement in the join statement for the table you are joining in, a CASE statement in a temp table where all values are changed to match, or this handy little trick of using a CASE statement in the JOIN's ON clause.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.
How to create a join with the USING clause in Oracle? Use the USING clause to specify the columns for the equijoin where several columns have the same names but not same data types. Use the USING clause to match only one column when more than one column matches.
You're looking for IsNumeric but it doesn't always work (+,- and . are numeric) so you need to use the solution described by GBN which is to add .0e0 to your varchar
LEFT JOIN [CTSTRC] [Statuses] ON
(CASE WHEN ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0) = 1
THEN CAST(RIGHT([Statuses].[STRID], 3) AS INT)
ELSE 0 END) = [CTE].[F61]
create a persisted computed column and add an index on it.
ALTER TABLE YourTable ADD
NewIntID AS (CASE ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0)
WHEN 1 THEN CAST(RIGHT([Statuses].[STRID], 3) AS INT)
ELSE 0
END) PERSISTED
GO
CREATE INDEX IX_YourTable_NewIntID
ON YourTable (NewIntID );
GO
you can now just join to the new NewIntID column as if it were the proper numeric ID now.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With