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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
LD16Author Commented:
Sorry,

Please find attached revised screenshot:

INT_BEGINT.png
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 KumarDatabase 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ãoMSSQL 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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.