( plz help ) querying multi record block 1

[bilalch]

hi dear frends,

can any1 help me how to select data into multi record block as i'm getting trouble with this way.

select all ID,NAME,ADD
into :ID,:NAME,:ADD
from STUDENTS
where NAME like '%'||:NAME||'%';




thanx
bye

 

[BJCooperIT]

Can you give a clearer explanation of "trouble"?

Are you getting an error or the wrong results?

Is the block a base table block (associated with a table in it's property pallette)?

Querying a block involves using

execute_query

, not SQL. Are you trying to populate the rows or add data to existing rows?



[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[bilalch]

hi dear

thanx for ur response.

the problem is that i want the results to be shown on some button click.

the second thing is that i want the form to search for the value using the wild cards (%) instead of matching the whole key word then searching.

now the problem is that this query works well when the query results only one record. but in the case when there are multiple records in the query result. forms raise unhandeled exception error.

thats the whole story.

thanx
bye

 

[lewisp]

I really dont understand your problem. Why cant you put execute_query on a button click, and copy the wildcard values into base table items in a pre-query? Or alter the default WHERE clause on the block?

You dont specify what the unhandled exception is.

 

[bilalch]

sorry dear but i've tried it putting in the default where clause of the block.i've written it like this in the where clause.

:con.con_no like '%'||:con.con_no||'%'

and when i enter the substring i get , the query caused no results message.

can u plz give me some example which can solve the problems like this. or tell me how to copy the values into pre-query trigger , shall i write the whole sql statement or just the where clause.


thanx
bye

 

[BJCooperIT]

Is this the same question or something new?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[sem]

I think you need to familiarize yourself with Forms basic concepts, especially with the concept of base-table block, query-by-example and processing flowcharts. I think that you try to use an intelligent device like a hammer

Your condition

:con.con_no like '%'||:con.con_no||'%'

makes about no sence, because you're trying to compare forms item value with the same forms item value. This condition does not involve any database-related information. I'm sure you need something other.

Regards, Dima

http://seminihin.narod.ru

 

[bilalch]

sorry dear its a typo.

i've used it like.

con.con_no like '%'||:con.con_no||'%'

in the where clause of the block 'CON'



thanx
bye

 

[lewisp]

Have you aliased the table name in the base table block, or is your table called CON (not STUDENTS as in your example)?

 

[sem]

Another question: do you get some error message or just catched NO_DATA_FOUND?

Regards, Dima

http://seminihin.narod.ru

 

[bilalch]

hi dear frends , many cardial thanks to all of you for ur responses.

as for the 'STUDENTS' is concerned, i used it for an example to put this question at this forum.

CON is my original table having the attributes
CON_NO (of type varchar),CON_DATE (of type date),
CON_QTY ( Number) etc. of which i'm trying to match the CON_NO's having the same substring.

the query works well where query results only one record but where the query is expected to result more than one records.
i get no error but just
"query caused no records." message

i'm using developer2000 forms5.

thanks
bye

 

[lewisp]

Can you explain exactly how you have set up your block. What have you specified for the 'Database Data Block', 'Query Data Source Type' and 'Query Data Source Name' properties?

 

[sem]

Bilalch, I'd suggest you to start a new thread because the information you provide is contradictory: you used STUDENS table as an example, now it's CON; you stated that unhandled exception was raised, but now you have no errors. So where is truth? You make everything possible to obscure your problem.

Regards, Dima

http://seminihin.narod.ru

 

[bilalch]

hi dear lewisp .

i think the problem is some where in the use of where clause.plz suggest what to write in the where clause property of the block so that the user dont have to put the '%' sign in the text item for searching.

the values u asked are as under.

Database Data Block YES
Query Data Source Type TABLE
Query Data Source Name CON

* and DIMA hope i'll have to follow ur suggestion.

THANX & REGARDS.
bye

 

[sem]

You may change item value within pre-query trigger:

if :con.con_no is not null then
:con.con_no := '%'||:con.con_no||'%';
end if;

and leave WHERE clause blank

Regards, Dima

http://seminihin.narod.ru

 

[bilalch]

many many thanks DIMA u've solved my problem.

also many thanks to all of u guys , u tried to solve my
problem.