SQL: Error converting data type varchar to bigint

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])

Select allOpen 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])

Select allOpen in new window

Edited my last comment.

Sorry,

Please find attached revised screenshot:

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])

Select allOpen in new window

Is that worked?

I tried but it doesn't work.


Thank you for your help.

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 !

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.

Hello,

Sorry for the delay.
I proceed to select Best solution and assisted solution.

Vitor's comment is incorrect, Please see your screen shot from #a42362500.

Ok, my mistake, could you please reopen the question so I can properly attribute solutions?

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'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.

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

Your try_cast is failing.

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.

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


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.

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.

@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])

Select allOpen in new window