Add SQL query function
I would love for there to be an "Advanced" button somewhere in the query module that would allow you to simply type in an SQL query string. Not many people would use it, so it doesn't need to be present and front-facing all the time, but it should exist as an option for those who know how to write queries as text commands. It would certainly make creating a lot of the more straight forward queries easier.
-
Guest
- Aug 2 2014
Related ideas
Blackbaud recently pulled the View SQL option from our hosted database view and it's greatly hurt our abilities to figure things out with our onsite Data Warehouse.
I agree with the above.
I'm not finding the view SQL but I think it is because we are hosted on BB's servers. They have some differences between being hosted and not hosted.
I would love to be able to query "LIKE 'string*'".
Apparently, we can see the SQL query code.
I was just messing with a query, looked in the View menu bar item, and there was an SQL option.
Check this out...
SELECT
RECORDS.CONSTITUENT_ID "Constituent ID",
RECORDS.FIRST_NAME "First Name",
RECORDS.LAST_NAME "Last Name",
RECORDS.ORG_NAME "Organization Name",
T_1.ADDRESS_BLOCK "Preferred Address Lines",
T_1.CITY "Preferred City",
T_2.LONGDESCRIPTION "Preferred State",
T_1.POST_CODE "Preferred ZIP",
T_3.LONGDESCRIPTION "Preferred Country",
T_5.NUM "Preferred Phone Number",
T_6_4_267.NUM "Preferred Home Number",
T_7_4_268.NUM "Preferred Business Number",
T_8_4_272.NUM "Preferred Cell Phone Number",
T_9_4_270.NUM "Preferred Email Number",
RECORDS.ID "QRECID"
FROM
DBO.RECORDS AS RECORDS INNER JOIN DBO.CAPREFERRED AS RECORDS_CAPreferred ON RECORDS.ID = RECORDS_CAPreferred.CONSTIT_ID INNER JOIN DBO.ADDRESS AS T_1 ON RECORDS_CAPreferred.ADDRESS_ID = T_1.ID LEFT OUTER JOIN DBO.STATES AS T_2 ON T_1.STATE = T_2.SHORTDESCRIPTION LEFT OUTER JOIN DBO.TABLEENTRIES AS T_3 ON T_1.COUNTRY = T_3.TABLEENTRIESID LEFT OUTER JOIN DBO.CONSTIT_ADDRESS_PHONES AS T_4 ON RECORDS_CAPreferred.ID = T_4.CONSTITADDRESSID LEFT OUTER JOIN DBO.PHONES AS T_5 ON T_4.PHONESID = T_5.PHONESID LEFT OUTER JOIN DBO.tmp18649_1573833277_1 AS T_6_4_267 ON RECORDS_CAPreferred.ID = T_6_4_267.CONSTITADDRESSID LEFT OUTER JOIN DBO.tmp18650_1573833277_1 AS T_7_4_268 ON RECORDS_CAPreferred.ID = T_7_4_268.CONSTITADDRESSID LEFT OUTER JOIN DBO.tmp18651_1573833277_1 AS T_8_4_272 ON RECORDS_CAPreferred.ID = T_8_4_272.CONSTITADDRESSID LEFT OUTER JOIN DBO.tmp18652_1573833277_1 AS T_9_4_270 ON RECORDS_CAPreferred.ID = T_9_4_270.CONSTITADDRESSID
WHERE
(((RECORDS.CONSTITUENT_ID IS NOT NULL)) AND (RECORDS.IS_CONSTITUENT = -1))
ORDER BY
RECORDS.CONSTITUENT_ID DESC
It's quite the code, and I can't seem to edit it, but it could be a step.
This would be amazing! I can think of several times where the Query Tool overly complicated things, and frankly, I sometimes just don't trust it when I can't see the guts.
@Joshua Jackson, I can't see anything that gives me the option to view the query, what version are you on?
7.93.5782.11
Query - open a query - View - SQL
Alternatively, Ctrl+Q
Joshua, can you describe where you found the view menu bar item to see the SQL? I don't see it there. Thanks.
I just open a query...
View (like File, Edit, View) - SQL