SQL: Error converting data type varchar to bigint

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,

I am trying to perform the following query, however I have an error message.
SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

I don't how how should I cast my fields.
CONVERSION_FIELD.png
Thank you very much for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi,
Your query is different and screen shot is different.  Open a NEW Query window and run one of the query below.

Please run below-

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window


or

this ....Here I have converted it to the VARCHAR(MAX)

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON P.SPoTInternalID = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last comment.
Luis DiazIT consultant

Author

Commented:
Sorry,

Please find attached revised screenshot:

INT_BEGINT.png
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this-

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON CAST(P.SPoTInternalID AS VARCHAR(MAX)) = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Is that worked?
Luis DiazIT consultant

Author

Commented:
I tried but it doesn't work.

VARCHAR_BEGINT.png
Thank you for your help.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Some other column issue. Please try this. This should definitely work.

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON CAST(P.SPoTInternalID AS VARCHAR(MAX)) = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
WHERE ( CAST(P.[Version] AS VARCHAR(MAX)) = '1') and (CAST(P.Active AS VARCHAR(MAX)) = '1') and CAST(P.ID AS VARCHAR(MAX)) IN 
	(SELECT CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
After the above works then you can remove the casting one by and put only on the column which was creating the issue. I have done like that because I dont know the data types of your columns.

Thanks !
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
That means that you have non numeric values in that field. To filter only the values that can be converted to BIGINT. Use TRY_CAST instead of CAST:
SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
	INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM ON TRY_CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
LD16 - It appears that you've asked a duplicate question of this one.  In the future please keep comments in your original question and not post duplicate questions with the same ask.  Thanks in advance.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
@Author -
Have you executed my last suggestion-

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON CAST(P.SPoTInternalID AS VARCHAR(MAX)) = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
WHERE ( CAST(P.[Version] AS VARCHAR(MAX)) = '1') and (CAST(P.Active AS VARCHAR(MAX)) = '1') and CAST(P.ID AS VARCHAR(MAX)) IN 
	(SELECT CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window


I think below will also work-
SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON P.SPoTInternalID = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
WHERE (P.[Version] = '1') and (P.Active = '1') and CAST(P.ID AS VARCHAR(MAX)) IN (SELECT CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Luis DiazIT consultant

Author

Commented:
Hello,

Sorry for the delay.
I proceed to select Best solution and assisted solution.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Vitor's comment is incorrect, Please see your screen shot from #a42362500.
Luis DiazIT consultant

Author

Commented:
Ok, my mistake, could you please reopen the question so I can properly attribute solutions?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can do that by clicking on the report question link at the top of the question and then ask the moderators to open the question for you.
Thanks .
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Vitor's comment is incorrect, Please see your screen shot from #a42362500.
Low comment for earning points.
The comment that author's choose was where I told him to use TRY_CAST instead of CAST. Error was returned when author's used CAST.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Error was not there. Read my last comment again and compare the screen shots.

Your try_cast is failing.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Pawan, I just made one comment in this question and it was after author's comment #a42362500 that was even related to a solution that you provided and didn't work.
If you see any issue with my provided solution, please be specific about the error and don't just say it doesn't work. This way everybody can be clarified and perhaps learn from you.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Ok, I will make it clear to you and the Author.

VARCHAR_BEGINT.png
In the above screen shot the author is converting P.SPoTInternalID to Varchar, Infact both SPoTInternalID  and  NWSMGR_PRJ_N_EXPORT_PRJ_ONB . See It is still failing.
<ON CAST(P.SPoTInternalID AS VARCHAR(MAX)) = CAST(EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB AS VARCHAR(MAX))>
Now you are using Try_CAST there only (at the same place) so it will fail as it was failing for VARCHAR. This is because the error was not there.

I hope it is clear to you now.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I hope it is clear to you now.
No. Only with the screenshot with the TRY_CAST function will make it clear. Until there it's only an assumption from you. But the author can confirm that.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - A little help please.

Below is Vitor's code .. could you please run it and given me the screen shot of what you are getting.

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB,
EPM.NWSMGR_NEW_Spot_Project_Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
	INNER JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM 
 ON TRY_CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
WHERE (P.[Version] = '1') and (P.Active = '1') and P.ID IN (SELECT EPM.NWSMGR_PRJ_N_EXPORT_PRJ_ONB
FROM [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated])

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial