• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

query to select values from oracle db based on a value

Hello,

from my oracle db i would like to retreive data based on the combobox value, but the problem is want to retreive only the value starting after '.'

for eg:- if my combobox value is 1234, then i want to retreive from oracle 001, where in the oracle db the value is like 1234.001

so i would like to retreive only the values starting after '.'

im trying this but its not showing any result

can somebody please help, its little bit urgent

thanks

...........................................
oRec.Open "SELECT  substr(proj_nr1, instr(proj_nr1, '.')) FROM regdet where  substr(proj_nr1, 0, instr(proj_nr1, '.')-1)='" & result4 & "'", oConn
0
vihar123
Asked:
vihar123
2 Solutions
 
Ryan ChongCommented:
something like:

...
sqlstr = "SELECT substr(proj_nr1,1, instr(proj_nr1, '.')-1) FROM regdet where substr(proj_nr1,1, instr(proj_nr1, '.')-1) = '" & result4 & "' "
oRec.Open sqlstr, oConn
...


?
0
 
Ryan ChongCommented:
>>so i would like to retreive only the values starting after '.'
oops, try like:

SELECT  substr('1234.001', 1, instr('1234.001', '.')-1) ,  substr('1234.001', instr('1234.001', '.')+1) FROM
dual

? so become as:

...
sqlstr = "SELECT substr(proj_nr1, instr(proj_nr1, '.')+1) FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, '.')-1) = '" & result4 & "' "
oRec.Open sqlstr, oConn
...

hope this helps, regards
0
 
vihar123Author Commented:
query is working, i tried it in the sql and even my old one is working but its not populating the data.......something wrong with the code below??????


oRec.Open "SELECT substr(proj_nr1, instr(proj_nr1, ',')+1) FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, ',')-1) = '" & result4 & "'", oConn

    While Not oRec.EOF

   Tabelle2.ComboBox2.AddItem (oRec.Fields("proj_nr1").Value)
   oRec.MoveNext

   Wend
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
EDDYKTCommented:
do you see any value from oRec

ie

oRec.movefirst
While Not oRec.EOF

msgbox oRec("proj_nr1")
  Tabelle2.ComboBox2.AddItem (oRec("proj_nr1"))
  oRec.MoveNext

  Wend
0
 
vihar123Author Commented:
>>>

no its not showing any values
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Try this

oRec.Open "SELECT substr(proj_nr1, instr(proj_nr1, ',')+1)  AS RESULT FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, ',')-1) = '" & result4 & "'", oConn

    While Not oRec.EOF

        Tabelle2.ComboBox2.AddItem (oRec.Fields("RESULT").Value)
         oRec.MoveNext

   Wend

0
 
vihar123Author Commented:
thanks shijusn and others

its working

0
 
vihar123Author Commented:
one more small question the same above query if i want to retreive one more field, what should i do,

i mean (proj_nr1 and proj_desc)
0
 
Ryan ChongCommented:
>> one more small question the same above query if i want to retreive one more field, what should i do

You can try select the additional field like:

...
sqlstr = "SELECT substr(proj_nr1, instr(proj_nr1, ',')+1)  AS RESULT, proj_desc FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, ',')-1) = '" & result4 & "'"
...

cheers
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now