| querying block [message #135614] |
Thu, 01 September 2005 09:32  |
dmack
Messages: 42
Registered: March 2005
Location: TNT
|
Member |
|
|
Hi everyone
I am having a problem when querying my form I get the following error: invalid column name and this is the error displayed
SELECT ROWID,OFF_NO,OFF_NAME,LOCATION_IP FROM SERV_LOCATION WHERE :1 = dep_user and :2 = dep_prog_name
This is the code used
DECLARE
loc_cnt number(4) := 0;
BEGIN
IF :prog_check = 'Y' THEN (sw_details block)
SELECT count(dep_prog_name)
INTO loc_cnt
FROM temp_location
WHERE :prog_name = dep_prog_name
AND :location_ip = dep_location
AND :v_hd_user = dep_user;
IF loc_cnt > 0 THEN
go_block ('serv_location');
set_block_property ('serv_location',default_where,'where :global.hd_user_id = dep_user and :prog_name = dep_prog_name');
execute_query;
end if;
end if;
end;
What is supposed to happen is - one block is populated (sw_details)
when a check box is checked within this block then results should be displayed in another block (serv_location. This does not happen.
Please help (don't know if i have provided enough details)
|
|
|
|
| Re: querying block [message #135626 is a reply to message #135614] |
Thu, 01 September 2005 10:43  |
kiran
Messages: 503
Registered: July 2000
|
Senior Member |
|
|
Hi,
In your block query you have written column name as ":1" and ":2".You are not suppose to use the columns names as bind variables and more over with numeric digits.
So only you are getting an error.
--Kiran.
|
|
|
|
| Re: querying block [message #135662 is a reply to message #135626] |
Thu, 01 September 2005 13:46 |
dmack
Messages: 42
Registered: March 2005
Location: TNT
|
Member |
|
|
thank u
but i don't quite understand. You see i am dealing with multi record blocks in both cases (sw_details and serv_location) and the digits you referred to wasn't done by myself but I guess was assigned by the program. what confuses me however is the rowid, in the select statement I am not sure if that is the invalid column being referred to in the error message.
You query one block (sw_details), when the results are displayed, then a check box is checked on the same block this is the basis for the query of the other muti record block (serv_location). When the program name is checked this queries a table and returns the results in the other multi record block (serv_location).
The table being queried is not the base table for the block being queried.
Still confused hope u can help.....
|
|
|
|
|
|
| Re: querying block [message #135778 is a reply to message #135722] |
Fri, 02 September 2005 09:01 |
dmack
Messages: 42
Registered: March 2005
Location: TNT
|
Member |
|
|
Hi well as u may have guessed its still not working
my tables are
1. serv_location : off_no, off_name, location ip
2. temp_location :dep_user,dep_prog_name,dep_location,dep_transfer
3. sw_details:(this is a larger table, i will not describe this as this is not the focus of the problem)
my blocks are:
1. sw_details which is built off of the sw_details table
2. serv_location which is built off of the serv_location table
3. misc_block which is not built off of a table this block holds all other items.
TRIGGER : WHEN-CHECK-BOX-CHANGED
this trigger is part of the sw_details bloc on the item prog_check.
DECLARE
loc_cnt number(4) := 0;
BEGIN
IF :prog_check = 'Y' THEN
SELECT count(dep_prog_name)
INTO loc_cnt
FROM temp_location
WHERE dep_prog_name = :prog_name
AND dep_location= :location_ip
AND dep_user = :v_hd_user;
IF loc_cnt > 0 THEN
go_block ('serv_location');
set_block_property ('serv_location',default_where,'where temp_location.dep_location = serv_location.location_ip and misc_block.global.hd_user_id = dep_user and sw_details.prog_name = dep_prog_name');
execute_query;
:destinationcheck:= 'Y';
end if;
end if;
end;
I have attached a copy of waht the form looks like:
How the form works:
1.In the Text item a call # is entered :501 (this is part of the misc_block)
2.When the number is entered the relevant programs associated with the call number is poulated in the multi record block (sw_details)
3.When the form is run the serv_location block is poulated with locations for which the programs in the above block are to be deployed.
4.Then the program or programs to be deployed is checked (prog_check is the name of the item)and the locations to be deployed are also checked (destinationcheck)
5. when this is done the program name or names along with the location i.e office , office code and ip address are inserted into the temp_location table.
6. This works fine however before deploying the programs the user is allowed to query which programs are going to which location, which brings me to my problem. When the form is run and a call number is enetered and the related programs are displayed and a program name is checked, it should display the locations that were selected for that program (query is only being done for one program at a time). This does not happen, what happens is that I check the program and the block is empty. When i check the display error i get the error previously mentioned.
I do hope this makes the problem much clearer.
-
Attachment: deploy.doc
(Size: 42.50KB, Downloaded 1306 times)
|
|
|
|
|
|
|
|
| Re: querying block [message #136147 is a reply to message #135614] |
Tue, 06 September 2005 06:49 |
dmack
Messages: 42
Registered: March 2005
Location: TNT
|
Member |
|
|
Hi basically i think initially i used the wrong trigger (I used when check box changed)in the sw_details block.
After some advice from some other colleagues I decided to use the post query trigger on the serv_location block (using the same code with the exception of the where clause). What this did was after the query was performed went through every single record and based on the condition, assigned the destinationcheck as 'Y' where the program existed for that particular location.
I also removed the where clause this restricted what was retrieved from the query and caused some problems.So now when i select a program by checking the check box (prog_check). The results are displayed in my serv_location block indicating where the program was initially selected to be deployed by the destinationcheck being checked against it.
If any more clarification is needed feel free to ask.
bye .....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
