Home » SQL & PL/SQL » SQL & PL/SQL » sql query (oracle 10g)
sql query [message #348615] Wed, 17 September 2008 07:35 Go to next message
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 #348620 is a reply to message #348615] Wed, 17 September 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: sql query [message #348627 is a reply to message #348620] Wed, 17 September 2008 07:54 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Better create trigger to check presence of the existence data.
Re: sql query [message #348629 is a reply to message #348627] Wed, 17 September 2008 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
panyam wrote on Wed, 17 September 2008 14:54

Better create trigger to check presence of the existence data.

Explain the relation between the question and your answer.

Regards
Michel

Re: sql query [message #348633 is a reply to message #348629] Wed, 17 September 2008 08:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #348886 is a reply to message #348634] Thu, 18 September 2008 04:02 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
this should be handled in a single query. No function.
Re: sql query [message #348888 is a reply to message #348886] Thu, 18 September 2008 04:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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


Previous Topic: Help in this SQLl
Next Topic: which block will execute faster. please suggest
Goto Forum:
  


Current Time: Fri Dec 09 02:14:39 CST 2016

Total time taken to generate the page: 0.05692 seconds