Possible points of failure: (1) CAST (LVRa.Rev_cd as INTEGER) - If Rev_cd contains a value or values that cannot be converted to integer, you will get an error. (2) INNER JOIN ar_asrh_ap_etl_beacon.Lv_Rev2 LVRb ON LVRa.Rev_cd = Lvrb.Rev_id - If Rev_cd is character and Rev_Id is numeric, and if any Rev_cd's cannot be cast to numeric, you will get an error.

I have gotten a bit lost among the emails. Have you confirmed what data types Rev_Id and Rev_cd are?? Here is another way you can tell.

I believe the datatype of Rev_id is integer. But i can see a space between 46 and 0 (46 0). Because of this you are finding the bad character issue as the data you are trying to match doesnot corresponds to datatype of Rev_id

Distinct actually works faster than group by. Group by is suggested to use when you need to perform some aggregate calculations. As the intention here is to just remove duplicates distinct best suits the purpose. You can check this by running explain command.

Possible points of failure:
(1) CAST (LVRa.Rev_cd as INTEGER) - If Rev_cd contains a value or values that cannot be converted to integer, you will get an error.
(2) INNER JOIN ar_asrh_ap_etl_beacon.Lv_Rev2 LVRb ON LVRa.Rev_cd = Lvrb.Rev_id - If Rev_cd is character and Rev_Id is numeric, and if any Rev_cd's cannot be cast to numeric, you will get an error.

I have gotten a bit lost among the emails. Have you confirmed what data types Rev_Id and Rev_cd are?? Here is another way you can tell.

For the sake of those of us who have tried to assist you and for those who may be following this thread in order to learn from it, could you please explain what you did or changed in orer to be able to get what you were looking for?

There have been many suggestions and you have posted several UNsuccessful attempts, so how about posting your SUCCESSFUL attempt? ;-)

One last comment. I have found, over the years, that it sometimes pays to simplify what one is trying to do. In this case, I would probably create the table with a CRETE TABLE statement, thus insuring that I know exactly what datatypes and sizes will be in the table, prior to trying to insert data into it. (While I will agree that the SELECT . . . INTO> > > seems to be a much faster approach, you might want to consider the number of hours you have spent trying to make it work. ;-)

Once the table is created, it is a relatively simple task to create the SELECT statement to pull the desired data and it lets you experiment a bit while also letting you actually see the data being selected. Adding the INSERT clause above the SELECT is then, also, a simple task.

If you highlight and execute the SELECT statement in the above, you should
be able to view what it is retrieving. If there are issues (e.g.
errors or not getting the desired results) with the SELECT statement, then you can
either comment out portions or build up the SELECT in a separate execution window
until it gets what you want and then paste it back into the above.

The real key is that, by viewing the data that is returned, you can get
a much better idea as to what is going on.