| sql query [message #348615] |
Wed, 17 September 2008 07:35  |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
i have 4 columns say a,b,c,d type number in a table. All columns are unique.
data inserted as
a,b,c,d
1,2,3,4
5,6,7,8
i try to insert now
a) 1,6,7,8 my output result should be 1,1,1,1
b) 9,6,7,4 my output result should be 0,1,1,1
how this is possible in a single query
|
|
|
|
|
|
|
|
|
|
| Re: sql query [message #348633 is a reply to message #348629] |
Wed, 17 September 2008 08:03  |
panyam
Messages: 146
Registered: May 2008
|
Senior Member |
|
|
Sorry michel i thought he just need to check the existnece of data ( which he is trying to insert now ) if it is present then o/p should be "1" else "0"
is it not ???
|
|
|
|
| Re: sql query [message #348634 is a reply to message #348629] |
Wed, 17 September 2008 08:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The Op seems to be looking for some sort of uniqueness check on the data in his columns.
The (1,1,1,1) and (0,1,1,1) are whether or not the values in those columns are unique.
Nowm whether he wants thos values to come back from a function, or whether he just wants the values that he inserts to be overwritten, wwe will have to wait and see.
|
|
|
|
|
|
| Re: sql query [message #348888 is a reply to message #348886] |
Thu, 18 September 2008 04:05 |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
|
Idea is to check the uniqueness in a single query for all the 4 columns. this could have been done with 4 individual sql statement , but then I am hitting the database 4 times querying from same table.
|
|
|
|
| Re: sql query [message #348893 is a reply to message #348888] |
Thu, 18 September 2008 04:16 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Michel Cadot wrote on Wed, 17 September 2008 14:37 | ...
Post a test case: create table and insert statements along with the result you want with these data.
Explain your result.
Regards
Michel
|
[Updated on: Thu, 18 September 2008 04:16] Report message to a moderator |
|
|
|
| Re: sql query [message #348894 is a reply to message #348888] |
Thu, 18 September 2008 04:20 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What do you want to happen if one of the alues is not unique?
Do you want the insert to fail, or do you want the non-unique column to be set to null, or what?
Assume we have absolutely no idea what you want, and try and explain your wishes (ideally with examples)
|
|
|
|
| Re: sql query [message #348901 is a reply to message #348615] |
Thu, 18 September 2008 04:31 |
dr.s.raghunathan
Messages: 540
Registered: February 2008
|
Senior Member |
|
|
hi subusona,
you have created lots of interest on your querry. Please reply to messages offered by JRowBotton and to others. Me too curious
yours
dr.s.raghunathan
|
|
|
|
| Re: sql query [message #348903 is a reply to message #348894] |
Thu, 18 September 2008 04:35 |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
since unique key is defined on individual columns insert is going to fail anyway if duplicate values are given.
scenario: a bulk upload where all the four columns are passed in array.
while inserting if the values on any of the 4 columns are duplicate I want capture those values, again in an array as out parameter.
for i in ...
loop
select count(1) into t from <<table_name>>
where a=9 -- this can be either 0 or 1
if t=1 then
--put "9" in a_array
else
--do nothing
end if;
select count(1) into t from <<table_name>>
where b=6 -- this can be either 0 or 1
if t=1 then
--put "6" in b_array
else
--do nothing
end if;
select count(1) into t from <<table_name>>
where c=7 -- this can be either 0 or 1
if t=1 then
--put "7" in c_array
else
--do nothing
end if;
select count(1) into t from <<table_name>>
where d=4 -- this can be either 0 or 1
if t=1 then
--put "4 "in d_array
else
--do nothing
end if;
from the above i have made 4 sql statement to satisfy my need.
I only want a single query to satisfy all four.
|
|
|
|
| Re: sql query [message #348907 is a reply to message #348903] |
Thu, 18 September 2008 04:43 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Michel Cadot wrote on Wed, 17 September 2008 14:37 | please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Post a test case: create table and insert statements along with the result you want with these data.
Explain your result.
Regards
Michel
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
