Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql query

Posted on 2012-08-28
9
Medium Priority
?
627 Views
Last Modified: 2012-08-31
I was checking below command

select count(*) into <variable_1> from dual where exists (select null from XYX_Table where col1=<variable_2> and col2=<variable_3>);


I am getting 1 even when record is there or not from java program.

And getting 0 from toad always. Please advise
0
Comment
Question by:gudii9
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38343427
Either your variables aren't the same or you aren't looking at the same table between Java and Toad.
0
 
LVL 21

Assisted Solution

by:Amitkumar P
Amitkumar P earned 500 total points
ID: 38344048
whenever you use the count() only the field in the select clause, it will always return a single row.

by the way can you elaborate more on your requirement?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38344199
you are coding a COUNT() from DUAL.... that will always be 1 (or 0, depends on the EXISTS)

to have the actual row count, you would need:

select count(*) into <variable_1>from XYX_Table where col1=<variable_2> and col2=<variable_3>; 

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 500 total points
ID: 38344200
Also just ensure that your variable names are not the same as the column names in your actual sql statement.

select count(*) into <variable_1> from dual where exists (select null from XYX_Table where col1=<variable_2> and col2=<variable_3>);

also in order to test the sql, can you hardcode the variable values and test it out both in java/toad to see the output behaviour.

select count(*) into variable_1 from dual where exists (select null from XYX_Table where col1=10 and col2=20);
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38344207
checking your other question:

http://www.experts-exchange.com/Programming/Languages/Java/Q_27845562.html

I presume the "1" or "0" you get is the java's Row count affected info from the execution of the sql
0
 
LVL 7

Author Comment

by:gudii9
ID: 38346124
>>>by the way can you elaborate more on your requirement?


My requirement is if the count is 0 that means if that record is not existing in the database I need to generate the missing record report and send email to the user saying these records are not present in the database. Please advise
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38346262
so, as I wrote above: you have to count on the table directly, and not using DUAL table.
0
 
LVL 7

Author Comment

by:gudii9
ID: 38346373
When I tried
>>>select count(*) into total from xyz where col1='PPPP' and col2='7777'

it is erroring out ' missing keyword' and not executing through toad. Then suddenly complains missing licence eventhough i have licence.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38346500
>>so, as I wrote above: you have to count on the table directly, and not using DUAL table.

As long as they ony wat a boolean of a 0 or 1 it should work.

>>My requirement is if the count is 0 that means if that record is not existing in the database

Are you sure angelIII is not correct in the post http:#a38344207 and you are getting a 1 as the rows affected and not the actual count returned?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question