Hi, I have searched the very useful database you have here and although
given some direction i still have a problem with a query i am working on.
any help given would be greatly appreciated!
the premise of query i am working on requires that a user be prompted for a
variable found on table 3. 2 columns of data from 2 different tables (table
1 and table 2) be displayed - all null values on each of those must be
labeled instead of just having a blank space.
i have not found a way to use 2 outer joins on the same tables so i have
used a UNION operator to combine my results - this all works fine and
displays what it should - however i need to put in a line where the query
will prompt the user with a variable to filter the data. here is my
solution so far.. (i have replaced the specific data where possible with
generic ones for reading ease)
--start query
select COALESCE(a.column_1, 'NONE ASSIGNED') as "Column 1", b.column_2 as
"Column 2"
from table_1 a, table_2 b
where b.table_id = a.table_id(+)
UNION
select a.column_1 as "Column 1", COALESCE(b.column_2, 'NO PERSON ASSIGNED')
as "Column 2"
from table_1 a, table_2 b
where a.table_id = b.table_id(+);
--end query
the results look something like this:
Column 1 Column 2
100 John
200 NO PERSON ASSIGNED
300 Smithy
NONE ASSIGNED Wayne
etc etc..
i want the query to prompt the user for a variable (lets say "name of
country") - which is from a third table joined to the other 2, so it only
filters those results. however because of the UNION clause, it will only
filter the results from one of the 2 queries and will list the results from
the other query.
the line i have been experimenting is:
where c.country_name = '&Country Name'
and adding table_3 c to the FROM clause and also joining them to the other
tables with WHERE c.xxx_id = b.xxx_id
cliff notes - is there any way to prompt the user to input a variable and
then listing the relevant results from both select statements in the UNION
query...?
I am not sure if i am articulating this problem in the best possible way,
my apologiesThis is a Microsoft SQL Server group but your code is for Oracle. Are you
looking for a conversion to Transact-SQL?
As regards prompting the user, Transact-SQL doesn't have any UI
functionality. You have to prompt the user using your client application or
host programming language. Since you haven't specified what
application/language that is we can't really advise you. Please repost to
the correct group if you need more help.
David Portas
SQL Server MVP
--|||Are you going to tell me MS didn't receive any *wishes* for an
Sql*Minus (I mean Plus:) utility?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2uKdncChTPcRrw_fRVn-hQ@.giganews.com...
> This is a Microsoft SQL Server group but your code is for Oracle. Are you
> looking for a conversion to Transact-SQL?
> As regards prompting the user, Transact-SQL doesn't have any UI
> functionality. You have to prompt the user using your client application
> or host programming language. Since you haven't specified what
> application/language that is we can't really advise you. Please repost to
> the correct group if you need more help.
> --
> David Portas
> SQL Server MVP
> --
>
