aspose file tools*
The moose likes JDBC and the fly likes how to pass array value in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to pass array value in sql " Watch "how to pass array value in sql " New topic
Author

how to pass array value in sql

prajapatisagar Sagar
Ranch Hand

Joined: Feb 28, 2011
Posts: 35

this is the sql i am firing but the condition varible holds values like this '1001,1002,1003' but i need it to be like '1001','1002'..
so that query runs correctly
please help,thanks in advance
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30973
    
159

I've added code tags to make the post easier to read. I don't follow the problem though. Your condition is created by calling:


The values are in quotes. They are also in nvl() calls so you wouldn't see 1001, 1002, 1003 directly anyway.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
prajapatisagar Sagar
Ranch Hand

Joined: Feb 28, 2011
Posts: 35
Jeanne Boyarsky wrote:I've added code tags to make the post easier to read. I don't follow the problem though. Your condition is created by calling:


The values are in quotes. They are also in nvl() calls so you wouldn't see 1001, 1002, 1003 directly anyway.


please go through the codes and try if you can correct for my problems.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1851
    
  16

I only have time for a quick comment, but I think you're missing some commas between each of the "IN" values, and you have too many right parentheses. Your code looks like it would produce:

"...not in (nvl('1001','1'))nvl('1002','1')) [etc...])"

You need something like:

...not in (nvl('1001','1'),nvl('1002','1'), [etc...])"

Also, if you know you have values in your array, why use NVL() at all, as it will never fire anyway?

Generally, it's helpful to print out the SQL string you are generating, then try running that separately through your SQL interpreter, as this will prove whether your SQL is correct.


No more Blub for me, thank you, Vicar.
prajapatisagar Sagar
Ranch Hand

Joined: Feb 28, 2011
Posts: 35
chris webster wrote:I only have time for a quick comment, but I think you're missing some commas between each of the "IN" values, and you have too many right parentheses. Your code looks like it would produce:

"...not in (nvl('1001','1'))nvl('1002','1')) [etc...])"

You need something like:

...not in (nvl('1001','1'),nvl('1002','1'), [etc...])"

Also, if you know you have values in your array, why use NVL() at all, as it will never fire anyway?

Generally, it's helpful to print out the SQL string you are generating, then try running that separately through your SQL interpreter, as this will prove whether your SQL is correct.


thanks for your comment!!!

at first there is null value so i must check for null value
at second time the array returns (nvl('1001','1')) and this is fine
at third time the array returns (nvl('1001,1002','1')) so my sql is not running properly it should be like (nvl('1001','1002','1')) please help
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1851
    
  16

prajapatisagar Sagar wrote:
at first there is null value so i must check for null value
at second time the array returns (nvl('1001','1')) and this is fine
at third time the array returns (nvl('1001,1002','1')) so my sql is not running properly it should be like (nvl('1001','1002','1')) please help


You should work out your SQL separately e.g. via your SQL interpreter, so you can be sure your SQL will work. Have you done this? If so, show us the SQL you really want to run.

I don't have time to run your Java code or give you a SQL tutorial on NVL(), but as I said above, it looks like you may be producing the wrong format for your SQL, and in any case, the format you suggest here is also wrong. So it looks like you are building your SQL wrong, but this is just string manipulation so you should be able to figure that out for yourself.

Based on what you've told us, it sounds like the SQL for your IN...NVLs should like something like this:

With no real values to check: IN ( NVL('','1') )
With one value to check: IN ( NVL('1001', '1') )
With two values to check: IN ( NVL('1001', '1'), NVL('1002','1') )
With three values to check: IN ( NVL('1001', '1'), NVL('1002','1'), NVL('1003','1') )

You need a comma between each NVL(...) inside the IN(...) clause.
prajapatisagar Sagar
Ranch Hand

Joined: Feb 28, 2011
Posts: 35
chris webster wrote:
prajapatisagar Sagar wrote:
at first there is null value so i must check for null value
at second time the array returns (nvl('1001','1')) and this is fine
at third time the array returns (nvl('1001,1002','1')) so my sql is not running properly it should be like (nvl('1001','1002','1')) please help


You should work out your SQL separately e.g. via your SQL interpreter, so you can be sure your SQL will work. Have you done this? If so, show us the SQL you really want to run.

I don't have time to run your Java code or give you a SQL tutorial on NVL(), but as I said above, it looks like you may be producing the wrong format for your SQL, and in any case, the format you suggest here is also wrong. So it looks like you are building your SQL wrong, but this is just string manipulation so you should be able to figure that out for yourself.

Based on what you've told us, it sounds like the SQL for your IN...NVLs should like something like this:

With no real values to check: IN ( NVL('','1') )
With one value to check: IN ( NVL('1001', '1') )
With two values to check: IN ( NVL('1001', '1'), NVL('1002','1') )
With three values to check: IN ( NVL('1001', '1'), NVL('1002','1'), NVL('1003','1') )

You need a comma between each NVL(...) inside the IN(...) clause.


1>this is the query when user select first item.this is fine

select a.item_cd, a.item_desc,b.UNIT_DESC from item_mst a ,UNIT_MST b
where lower(a.item_desc) like lower('%') and a.item_cd not in ((nvl(''),'1'))
and a.UNIT_CD=b.UNIT_CD order by a.item_desc asc

2>this is the query when the user selects the second item.it does not show the item 10201045 this is fine.
select a.item_cd, a.item_desc,b.UNIT_DESC from item_mst a ,UNIT_MST b
where lower(a.item_desc) like lower('%') and a.item_cd not in ((nvl('10201045'),'1'))
and a.UNIT_CD=b.UNIT_CD order by a.item_desc asc

3>this is the query when user selects the third item.now the values is '10201045,10201004'.so the item 10201045 and 10201004 is show which should not be shown to users.

select a.item_cd, a.item_desc,b.UNIT_DESC from item_mst a ,UNIT_MST b
where lower(a.item_desc) like lower('%') and a.item_cd not in ((nvl('10201045,10201004'),'1'))
and a.UNIT_CD=b.UNIT_CD order by a.item_desc asc

the correct query need is like this

select a.item_cd, a.item_desc,b.UNIT_DESC from item_mst a ,UNIT_MST b
where lower(a.item_desc) like lower('%') and a.item_cd not in (nvl('10201045',1),nvl('10201004','1'))
and a.UNIT_CD=b.UNIT_CD order by a.item_desc asc
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to pass array value in sql