Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to pass array value in sql

 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic