SQL: Error converting data type varchar to bigint

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.
LVL 1
LD16Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
@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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
Edited my last comment.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
LD16Author Commented:
Sorry,

Please find attached revised screenshot:

INT_BEGINT.png
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
Is that worked?
0
 
LD16Author Commented:
I tried but it doesn't work.

VARCHAR_BEGINT.png
Thank you for your help.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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 !
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
LD16Author Commented:
Hello,

Sorry for the delay.
I proceed to select Best solution and assisted solution.
0
 
Pawan KumarDatabase ExpertCommented:
Vitor's comment is incorrect, Please see your screen shot from #a42362500.
0
 
LD16Author Commented:
Ok, my mistake, could you please reopen the question so I can properly attribute solutions?
0
 
Pawan KumarDatabase ExpertCommented:
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 .
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Error was not there. Read my last comment again and compare the screen shots.

Your try_cast is failing.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
@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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.