Start a new topic

save column from a resulting sql query into a variable

[This topic is migrated from our old forums. The original author name has been removed]

Ability to bind the results of a sql query's column into a variable that then can be used in future queries such as: select trunc(sysdate) /*var: currdate */ from dual; then you could write a query such as: select * from employee where registered > ${currdate||||String||where}$; Note, my use of /*var: */ is obviously just a sample notation and not a recommendation :)

[This reply is migrated from our old forums.]

Re: save column from a resulting sql query into a variable
Hi, Thanks for the suggestion. It's an interesting idea. The tricky part is to come up with a generic notation that is also easy to handle, given all the different SQL dialects supported by DbVisualizer. One notation that may be workable is something like this: ${currdate||select trunc(sysdate) from dual||String||vl=sql-1}$ In other words, a variable declaration where the "value" part is an SQL statement that creates a result set, and a "vl" ("value locator", already used for other purposes) option that identifies the value as an SQL statement and says that the variable should get its real value from the first column of the first row of the result set. You could then use the variable in another statement, just as in your example. Would something like this work for you? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: save column from a resulting sql query into a variable
I like your idea, but it would only work in a couple of my scenarios (but please do add it as a feature :) Let me give a better 'real world' example for my most common scenario: This is similar to some of the sql I run every day..... I have a username and want to pull *several* IDs all at once select u.user_id, d.department_id, u.manager_id from user u, d department where u.department_id = d.department_id and u.username = ${Username||||String||where}$; Because then I start poking around with those IDs on other tables, first, something simple: select * from user_history where user_id = ${User ID}$ but then I often want to use multiple IDs from the first query in a follow up query such as: select * from user where department_id = ${Dept ID}$ and manager_id = ${Manager ID}$ Right now I have to run the first query, write down all the IDs and then type them in in the later queries. Yes, I know I can rewrite the last sql using a where (select...) or even probably an inner join but this is unrealistic (for reasons I could go into if needed but don't want to clutter this up with right now) To wrap up (though I'm sure you get it), here is the first query against 'instrumented', stealing your 'vl' as an example: ]select u.user_id ${User ID||||||vl}$ , d.department_id ${Dept ID||||||vl}$, u.manager_id ${Manager ID||||||vl}$ from user u, d department where u.department_id = d.department_id and u.username = ${Username||||String||where}$;
[This reply is migrated from our old forums.]

Re: save column from a resulting sql query into a variable
Hi Jason, I understand that my suggestion is limited and that to be really useful, there must be a way to bind several columns from one result set to individual variables. I'll add this to our feature request system. Reading the description of your scenario, though, I wonder if the Navigator wouldn't be a great help to you: [http://www.dbvis.com/products/dbvis/doc/main/doc/ug/navigator/navigator.html] Have you tried it? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: save column from a resulting sql query into a variable
re: you suggestion: limited, BUT is very useful for certain circumstances, feel free to add my idea and your separately as requests ;) I haven't looked at the navigator before today, and it is now going to get a great workout as it solves some of needs, but still would like to suggest my original request for inclusion. Navigator is going to be great when I'm more 'browsing' around for stuff, but in most of my daily work I'm digging deep in multiple tables at the same time doing comparisons between tables and as such the queries that I'm working with that initiated my original idea often involved a from clause with 5-15 tables, all filtered down with 1 or 2 where clauses from variables (ignoring of course the join conditions) and a select list of columns spread across those tables. One other issue, which I'm not sure whether the navigator can deal with (haven't tried it yet) is that I've also got a couple of databases that I will soon be working with where the mandate from the DBAs is no referential integrity constraints. I'm assuming that is how the navigator knows how to move between tables. Thanks for your continued consideration :)
[This reply is migrated from our old forums.]

Re: save column from a resulting sql query into a variable
Hi Jason, > re: you suggestion: limited, BUT is very useful for certain circumstances, feel free to add my idea and your separately as requests ;) They will both be recorded as RFEs. > [?] > One other issue, which I'm not sure whether the navigator can deal with (haven't tried it yet) is that I've also got a couple of databases that I will soon be working with where the mandate from the DBAs is no referential integrity constraints. I'm assuming that is how the navigator knows how to move between tables. Yes, the Navigator relies on declared referential integrity constraints. We have as an RFE to allow for column name-based references as well, but currently it only works with PK/FKs. Best Regards, Hans