 | |
Select one field/value from two different tables
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Select one field/value from two different tables -
08-05-2008
, 12:06 PM
|
On Aug 4, 7:12?pm, t... (AT) panix (DOT) com (Dan Blum) wrote: I'm amazed that that actually parses. .....I got the same response from some other programmers in my company, .....including a DBA who, unfortunately, doesn't know code. .....Unfortunately, what worked in SQLPlus, from my original msg, doesn't work in Pro*C, .....which complains about the word "oldschool" in the larger query's concatentation. .....Neither SQLPlus nor Pro*C liked the union in the sub-select or the word "coalesce", .....in someone else's response. .....I also tried if, case, and decode, to no avail (they worked, but didn't give me what I wanted. .....I can't believe this is as complicated as it seems! |
statement with eithre of those included, someone can probably figure
out what the problem is - it's likely you just have the syntax slightly wrong
somewhere.
--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."
#22
| |||
| |||
|
|
I have two tables: "Old" and "New". Both table have the same primary key name and type: "PKEY", NUMBER. "Old" table has a column called "oldschool", while "New" table has a column called "newschool". Both columns are the same type (NUMBER) and cannot be null. I'm told that the two tables are mutually exclusive, such that the same PKEY value cannot exist in both tables, and that if it isn't in the "Old" table, then it must be in "New" table (i.e., it must exist in one or the other table, but not both). To get one or the other value, I tried the following, which seems to work in SQLPlus: SELECT (select oldschool from Old where pkey = 12345) || -- (i.e., concatenate) (select newschool from New where pkey = 12345) FROM DUAL ; In reality, I have to extract a LOT more data from a LOT more tables in the one query, so the above query isn't anywhere as simple as I've made it out to be (i.e., the "12345" value is replaced by another table's primary key which is common to "PKEY" in the above tables), but I want to know if the above will work, or will I get bogus or NULL values when I try it with the real and much larger query. Here's a sample of the larger query in Pro*C: select (many fields), (select oldschool from Old where pkey = MMAN.PKEY) || (select newschool from New where pkey = MMAN.PKEY), (many fields), FROM Middletable MMAN, (list of other tables), WHERE (stuff) ; Thanks! |
Wouldn't this work?
select (many fields), school,
(many fields),
FROM
Middletable MMAN,
(list of other tables),
-- inline view
(select oldschool school, pkey from Old
UNION
select newschool school, pkey from New ) schoolview
WHERE
MMAN.PKEY = schoolview.pkey
AND (stuff)
Ed
#23
| |||
| |||
|
|
I have two tables: "Old" and "New". Both table have the same primary key name and type: "PKEY", NUMBER. "Old" table has a column called "oldschool", while "New" table has a column called "newschool". Both columns are the same type (NUMBER) and cannot be null. I'm told that the two tables are mutually exclusive, such that the same PKEY value cannot exist in both tables, and that if it isn't in the "Old" table, then it must be in "New" table (i.e., it must exist in one or the other table, but not both). To get one or the other value, I tried the following, which seems to work in SQLPlus: SELECT (select oldschool from Old where pkey = 12345) || -- (i.e., concatenate) (select newschool from New where pkey = 12345) FROM DUAL ; In reality, I have to extract a LOT more data from a LOT more tables in the one query, so the above query isn't anywhere as simple as I've made it out to be (i.e., the "12345" value is replaced by another table's primary key which is common to "PKEY" in the above tables), but I want to know if the above will work, or will I get bogus or NULL values when I try it with the real and much larger query. Here's a sample of the larger query in Pro*C: select (many fields), (select oldschool from Old where pkey = MMAN.PKEY) || (select newschool from New where pkey = MMAN.PKEY), (many fields), FROM Middletable MMAN, (list of other tables), WHERE (stuff) ; Thanks! |
Wouldn't this work?
select (many fields), school,
(many fields),
FROM
Middletable MMAN,
(list of other tables),
-- inline view
(select oldschool school, pkey from Old
UNION
select newschool school, pkey from New ) schoolview
WHERE
MMAN.PKEY = schoolview.pkey
AND (stuff)
Ed
#24
| |||
| |||
|
|
I have two tables: "Old" and "New". Both table have the same primary key name and type: "PKEY", NUMBER. "Old" table has a column called "oldschool", while "New" table has a column called "newschool". Both columns are the same type (NUMBER) and cannot be null. I'm told that the two tables are mutually exclusive, such that the same PKEY value cannot exist in both tables, and that if it isn't in the "Old" table, then it must be in "New" table (i.e., it must exist in one or the other table, but not both). To get one or the other value, I tried the following, which seems to work in SQLPlus: SELECT (select oldschool from Old where pkey = 12345) || -- (i.e., concatenate) (select newschool from New where pkey = 12345) FROM DUAL ; In reality, I have to extract a LOT more data from a LOT more tables in the one query, so the above query isn't anywhere as simple as I've made it out to be (i.e., the "12345" value is replaced by another table's primary key which is common to "PKEY" in the above tables), but I want to know if the above will work, or will I get bogus or NULL values when I try it with the real and much larger query. Here's a sample of the larger query in Pro*C: select (many fields), (select oldschool from Old where pkey = MMAN.PKEY) || (select newschool from New where pkey = MMAN.PKEY), (many fields), FROM Middletable MMAN, (list of other tables), WHERE (stuff) ; Thanks! |
Wouldn't this work?
select (many fields), school,
(many fields),
FROM
Middletable MMAN,
(list of other tables),
-- inline view
(select oldschool school, pkey from Old
UNION
select newschool school, pkey from New ) schoolview
WHERE
MMAN.PKEY = schoolview.pkey
AND (stuff)
Ed
#25
| |||
| |||
|
|
I have two tables: "Old" and "New". Both table have the same primary key name and type: "PKEY", NUMBER. "Old" table has a column called "oldschool", while "New" table has a column called "newschool". Both columns are the same type (NUMBER) and cannot be null. I'm told that the two tables are mutually exclusive, such that the same PKEY value cannot exist in both tables, and that if it isn't in the "Old" table, then it must be in "New" table (i.e., it must exist in one or the other table, but not both). To get one or the other value, I tried the following, which seems to work in SQLPlus: SELECT (select oldschool from Old where pkey = 12345) || -- (i.e., concatenate) (select newschool from New where pkey = 12345) FROM DUAL ; In reality, I have to extract a LOT more data from a LOT more tables in the one query, so the above query isn't anywhere as simple as I've made it out to be (i.e., the "12345" value is replaced by another table's primary key which is common to "PKEY" in the above tables), but I want to know if the above will work, or will I get bogus or NULL values when I try it with the real and much larger query. Here's a sample of the larger query in Pro*C: select (many fields), (select oldschool from Old where pkey = MMAN.PKEY) || (select newschool from New where pkey = MMAN.PKEY), (many fields), FROM Middletable MMAN, (list of other tables), WHERE (stuff) ; Thanks! |
Wouldn't this work?
select (many fields), school,
(many fields),
FROM
Middletable MMAN,
(list of other tables),
-- inline view
(select oldschool school, pkey from Old
UNION
select newschool school, pkey from New ) schoolview
WHERE
MMAN.PKEY = schoolview.pkey
AND (stuff)
Ed
#26
| |||
| |||
|
|
On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote: I'm amazed that that actually parses. .....I got the same response from some other programmers in my company, .....including a DBA who, unfortunately, doesn't know code. .....Unfortunately, what worked in SQLPlus, from my original msg, doesn't work in Pro*C, .....which complains about the word "oldschool" in the larger query's concatentation. .....Neither SQLPlus nor Pro*C liked the union in the sub-select or the word "coalesce", .....in someone else's response. .....I also tried if, case, and decode, to no avail (they worked, but didn't give me what I wanted. .....I can't believe this is as complicated as it seems! In any case, what you want to do is replace the concatentation operator with UNION - or, if the tables are really mutually exclusive, UNION ALL (UNION removes duplicates, which if you don't have any duplicates just wastes time). E.g., (select oldschool from old where pkey = 12345 *union *select newschool from new where pkey = 12345)- Hide quoted text - - Show quoted text - |
available since version 9 and works just fine on our system which for
the purpose of my test was 9.2.0.6 on AIX 5.3.
Since you are having trouble putting your full query together try
writing the different parts separately then merging them into one SQL
statement. Consider inline views, scalar subqueries, union | union
all, case | decode depending on your version when you determine how to
put the working pieces together into one unified query.
You should be able to select from new union all select from old and
join this to whatever other queries you need if your version does not
support sclar sub-queries.
HTH -- Mark D Powell --
#27
| |||
| |||
|
|
On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote: I'm amazed that that actually parses. .....I got the same response from some other programmers in my company, .....including a DBA who, unfortunately, doesn't know code. .....Unfortunately, what worked in SQLPlus, from my original msg, doesn't work in Pro*C, .....which complains about the word "oldschool" in the larger query's concatentation. .....Neither SQLPlus nor Pro*C liked the union in the sub-select or the word "coalesce", .....in someone else's response. .....I also tried if, case, and decode, to no avail (they worked, but didn't give me what I wanted. .....I can't believe this is as complicated as it seems! In any case, what you want to do is replace the concatentation operator with UNION - or, if the tables are really mutually exclusive, UNION ALL (UNION removes duplicates, which if you don't have any duplicates just wastes time). E.g., (select oldschool from old where pkey = 12345 *union *select newschool from new where pkey = 12345)- Hide quoted text - - Show quoted text - |
available since version 9 and works just fine on our system which for
the purpose of my test was 9.2.0.6 on AIX 5.3.
Since you are having trouble putting your full query together try
writing the different parts separately then merging them into one SQL
statement. Consider inline views, scalar subqueries, union | union
all, case | decode depending on your version when you determine how to
put the working pieces together into one unified query.
You should be able to select from new union all select from old and
join this to whatever other queries you need if your version does not
support sclar sub-queries.
HTH -- Mark D Powell --
#28
| |||
| |||
|
|
On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote: I'm amazed that that actually parses. .....I got the same response from some other programmers in my company, .....including a DBA who, unfortunately, doesn't know code. .....Unfortunately, what worked in SQLPlus, from my original msg, doesn't work in Pro*C, .....which complains about the word "oldschool" in the larger query's concatentation. .....Neither SQLPlus nor Pro*C liked the union in the sub-select or the word "coalesce", .....in someone else's response. .....I also tried if, case, and decode, to no avail (they worked, but didn't give me what I wanted. .....I can't believe this is as complicated as it seems! In any case, what you want to do is replace the concatentation operator with UNION - or, if the tables are really mutually exclusive, UNION ALL (UNION removes duplicates, which if you don't have any duplicates just wastes time). E.g., (select oldschool from old where pkey = 12345 *union *select newschool from new where pkey = 12345)- Hide quoted text - - Show quoted text - |
available since version 9 and works just fine on our system which for
the purpose of my test was 9.2.0.6 on AIX 5.3.
Since you are having trouble putting your full query together try
writing the different parts separately then merging them into one SQL
statement. Consider inline views, scalar subqueries, union | union
all, case | decode depending on your version when you determine how to
put the working pieces together into one unified query.
You should be able to select from new union all select from old and
join this to whatever other queries you need if your version does not
support sclar sub-queries.
HTH -- Mark D Powell --
#29
| |||
| |||
|
|
On Aug 4, 7:12*pm, t... (AT) panix (DOT) com (Dan Blum) wrote: I'm amazed that that actually parses. .....I got the same response from some other programmers in my company, .....including a DBA who, unfortunately, doesn't know code. .....Unfortunately, what worked in SQLPlus, from my original msg, doesn't work in Pro*C, .....which complains about the word "oldschool" in the larger query's concatentation. .....Neither SQLPlus nor Pro*C liked the union in the sub-select or the word "coalesce", .....in someone else's response. .....I also tried if, case, and decode, to no avail (they worked, but didn't give me what I wanted. .....I can't believe this is as complicated as it seems! In any case, what you want to do is replace the concatentation operator with UNION - or, if the tables are really mutually exclusive, UNION ALL (UNION removes duplicates, which if you don't have any duplicates just wastes time). E.g., (select oldschool from old where pkey = 12345 *union *select newschool from new where pkey = 12345)- Hide quoted text - - Show quoted text - |
available since version 9 and works just fine on our system which for
the purpose of my test was 9.2.0.6 on AIX 5.3.
Since you are having trouble putting your full query together try
writing the different parts separately then merging them into one SQL
statement. Consider inline views, scalar subqueries, union | union
all, case | decode depending on your version when you determine how to
put the working pieces together into one unified query.
You should be able to select from new union all select from old and
join this to whatever other queries you need if your version does not
support sclar sub-queries.
HTH -- Mark D Powell --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.