SELECT FROM WHERE char column with mixed string and integers values throwing conversion failed conve-CodePudding

I haven't found a question similar to mine, probably because I'm not even sure how to ask it, but I have this table, with several columns, many of them of type char(4).

The column with the issue has values like these:

|PRODUCT_CATEGORIES06|
|--------------------|
|FNCY|
|   2|
|   3|
|GRN |
|STD |
|    |
|COMB|
|   1|
|CHO |

So, when I do: Fiddle

SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE RPO.REFERENCE_NUMBER = '00012122'
GROUP BY RPO.REFERENCE_NUMBER, PM.COMMODITY, PM.PRODUCT_CATEGORIES01, PM.PRODUCT_CATEGORIES06

I get:

|REFERENCE_NUMBER   |PRODUCT_CATEGORIES06|
|-------------------|--------------------|
|00012122           |1                   |
|00012122           |3                   |
|00012122           |GRN                 |

BUT when I do: Fiddle

SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE PM.PRODUCT_CATEGORIES06 = 1 
  AND RPO.REFERENCE_NUMBER = '00012122'
GROUP BY RPO.REFERENCE_NUMBER, PM.COMMODITY, PM.PRODUCT_CATEGORIES01, PM.PRODUCT_CATEGORIES06

It tries to convert GRN to int and throws this error:

Conversion failed when converting the varchar value 'GRN ' to data type int.

I have tried using the CHAR function, but I'm just shooting in the dark, I don't really know what to do.

I know the design is bad, buy I didn't do this, and also, I cannot change it.

How can I fix the query?

EDIT Added fiddle links

or try:

CREATE TABLE [dbo].[Table1](
    [REFERENCE_NUMBER] [char](8) NOT NULL,
    [PRODUCT] [char](12) NOT NULL,
)

CREATE TABLE [Table2](
    [PRODUCT] [char](12) NOT NULL,
    [PRODUCT_CATEGORIES06] [char](4) NOT NULL
)

INSERT INTO Table1 (REFERENCE_NUMBER, PRODUCT) VALUES ('REF1','PRODUCT1') 
INSERT INTO Table1 (REFERENCE_NUMBER, PRODUCT) VALUES ('REF1','PRODUCT2')
INSERT INTO Table1 (REFERENCE_NUMBER, PRODUCT) VALUES ('REF1','PRODUCT3')

INSERT INTO Table2 (PRODUCT, PRODUCT_CATEGORIES06) VALUES ('PRODUCT1',1)
INSERT INTO Table2 (PRODUCT, PRODUCT_CATEGORIES06) VALUES ('PRODUCT2',2)
INSERT INTO Table2 (PRODUCT, PRODUCT_CATEGORIES06) VALUES ('PRODUCT3','GRN')

--works without product category on where
SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE RPO.REFERENCE_NUMBER = 'REF1'
GROUP BY RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06

-- doesn't work, this error I'm trying to find a solution
SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE PM.PRODUCT_CATEGORIES06 = 1
AND RPO.REFERENCE_NUMBER = 'REF1'
GROUP BY RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06

CodePudding user response：

This line

PM.PRODUCT_CATEGORIES06 = 1

Makes SQL Server attempt to convert all values to an int, because 1 unquoted is an int. Correct it to a string, since your column data is a string:

PM.PRODUCT_CATEGORIES06 = '1'

And it works.