Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Cannot implicitely [sic] convert

I'm getting the following error when trying to create an extra JOIN.

Cannot implicitely convert from data type Guid to data type Inst32

Ignoring the spelling mistook I think I understand why. The two JOINs look like this:

JOIN Orion.NodesCustomProperties AS ncp ON nd.NodeID = ncp.NodeID
JOIN NCM.EntityPhysical AS ep ON ncp.NodeID = ep.NodeID

If I comment out the bottom one, it all works and that's because because the NodeID in ncp and ep are not compatible (I guess one is Guid and the other Inst32). One looks like 1234 and the other like: 12375e26-9d82-4437-b7c7-000d643d68cb

Abd SO my question is:

How do I JOIN in a table where I can't see a connection / similarity (e.g. NodeID)?

Find more posts tagged with

Accepted answers

All comments

Seashore

Hi, you have to go via the table NCM.Nodes. Like this:

SELECT
ND.Caption
,EP.EntityName
FROM Orion.Nodes AS ND
INNER JOIN NCM.Nodes AS NCMND ON ND.NodeID=NCMND.CoreNodeID
INNER JOIN NCM.EntityPhysical AS EP ON NCMND.NodeID=EP.NodeID

KMSigma.SWI

Step 1: Use Navigation Properties instead of JOINs where you can

SELECT [Nodes].NodeID
     , [Nodes].IPAddress
     , [Nodes].Caption
     , [Nodes].CustomProperties.City -- Uses a Navigation Property
FROM Orion.Nodes AS [Nodes]

Step 2: Find the CoreNodeID (used by the NCM entities)

SELECT [NCM_EP].EntityName
     , [NCM_EP].EntityDescription
     , [NCM_EP].Node.CoreNodeID -- <- Also a Nav Property
                                -- Used by Everything 'except' NCM
FROM NCM.EntityPhysical AS [NCM_EP]</pre>Step 3: We'll have to use a JOIN here, but it's easy if you go with the above:SELECT [Nodes].NodeID
     , [Nodes].IPAddress
     , [Nodes].Caption
     , [Nodes].CustomProperties.City
     -- From NCM things
     , [NCM_EP].EntityName
     , [NCM_EP].EntityDescription
     , [NCM_EP].Node.CoreNodeID
FROM Orion.Nodes AS [Nodes]
INNER JOIN NCM.EntityPhysical AS [NCM_EP]
  ON [NCM_EP].Node.CoreNodeID = [Nodes].NodeID

Long story short - stop using JOINs if you have ANY other alternative.

stuartd

[quote userid="2109" url="~/product-forums/the-orion-platform/f/orion-sdk/93265/cannot-implicitely-sic-convert/295713#295713"]Long story short - stop using JOINs if you have ANY other alternative.[/quote]

Thanks @KMSigma.SWI but that presumes I know what you mean by navigational properties

Are you aware of a resource that will take me on an a.b.c. through them, please?

stuartd

Thank you Thomas.

If I understand you, you're saying I have to do two JOINs to get to what I need? First goes to NCM.Nodes which can then "onwards" connect to NCM.EntityPhysical.

KMSigma.SWI

There's an article that explains how they work. I'm working on more detailed documentation (an API ebook), but the timeline on that is still forthcoming.

stuartd

Thank you again Kevin.

WRT the book - I literally just caught up on the webinar recording this morning and heard that, and thought ooo nice.