Hi, I joined an organization that connects Toad Data Point to Salesforce. I took a basic SQL class a few years ago so I am a beginner. There are two queries I use in Workbench/SOQL that I am having issues with in Toad.
Query 1 shows error "No such column 'Type' on entity 'PermissionSet'" in Toad.
Query 2 shows Labels as null in Toad. It also generates 81 results vs 82 in Workbench.
Query 1
SELECT Label,Name, Description
FROM PermissionSet
WHERE Type = 'Regular' or Type = 'Session' or Type = 'Standard'
ORDER BY Label
Query 2
SELECT PermissionSet.Label, Count(id)
FROM PermissionSetAssignment
WHERE Assignee.IsActive = TRUE AND PermissionSet.Label != ''
GROUP BY PermissionSet.Label
ORDER BY PermissionSet.Label
Not a SalesForce expert, but since "Type" is a common keyword in many languages, you might want to quality it with the table that contains it.
e.g. For your query #1, is there a PermissionSet.Type column?
For query #2,
Not sure what's going on there, but what happens if you comment out the Group By and Order By? Do you still get NULLs? and still get incorrect row count? TDP simply passes the query to the back end database, for execution, so not sure why your getting a different result set.
FYI, I recently installed the Professional Edition and used a license.
Interesting about Type being a common keyword. I have also tried using 'Type', "Type" and [Type] in the original query. I also did a refresh.
If this matters, this works in Workbench (Salesforce) but same issue in Toad.
SELECT PermissionSet.Type
FROM PermissionSetAssignment
I see text (not null) if I just query on Labels. See these two queries and how they compare. No issues with the second query.
SELECT PermissionSet.Label, Count(id)
FROM PermissionSetAssignment
WHERE Assignee.IsActive = TRUE AND PermissionSet.Label = 'Delete Access'
GROUP BY PermissionSet.Label
ORDER BY PermissionSet.Label
SELECT PermissionSet.Label
FROM PermissionSetAssignment
WHERE Assignee.IsActive = TRUE AND PermissionSet.Label = 'Delete Access'