Search results for query: *

I managed to solve the problem using lexical parameters, replacing the statement and e.org_id in (:p_orgs) with &org and passing the parameter &org as a string constructed using a function in my application. So in the end I didn't need to use a ref cursor query.

Hello, I have a ref cursor report which works fine apart from one line of the query: and e.org_id in (:p_orgs) After asking on the Oracle 8i forum I understand that this is not possible, and that I need to concatenate the bind variable with the rest of the query...

PS - I will give you a star from home; my work firewall forbids it!

Hi Dagon, thanks for the advice - I will re-think my approach; the ref cursor looks like the best idea. Thanks again, Hazel

Hello, I have a stored sql query used in a report. Parameters are passed to it and all work apart from this line: and e.org_id IN (:p_orgs) e.org_id is a numeric column in the table. :p_orgs is a string that can be blank, or can contain any number of integers separated by commas (e.g. "18...

Hi Santa, I totally agree and usually do give someone a star. However when I tried to yesterday I found that our network security guys had placed a restriction on it - maybe because it was classed as "voting" for something, I'm not sure. So instead of the usual popup I got a content violation...

Brilliant, it worked! So simple too, why didn't I think of that? [wink] Thanks very much Dagon.

Hi guys, just a quick question (hopefully). This is my desired query (simplified): select p.dm_id, x.title from post p, post_desc x where p.post_num = trim(x.post_no)(+) But I get the error "ORA-00933: SQL command not properly ended" because of the use of the trim function before the...

Hi Santa, the table "zcmintranet" is part of our Human Resources system - the "cmi" stands for "car mileage intranet". I'm not too sure why it's prefixed with a "z" - possibly because it's an extra table not included in the standard build of the application. All fields in the table are...

Wow, I never would have thought of that! It works perfectly, thanks a million. Hazel

Hi all, I've devised a CASE statement for use within my stored procedure SQL insert query but then found out it doesn't work in our version of Oracle. (The error message is PLS-00103: Encountered the symbol "CASE" when expecting one of the following: ( - + mod not null others <an...

I seem to have a talent for compicating things! [blush] Thanks very much for your help dbtoo2001, it's just what I needed. Cheers Hazel

Hi guys, I have two tables: cm_std_locations loc_code char(10) loc_desc varchar2(50) cm_std_distance loc1 char(10) loc2 char(10) distance number I have a query as follows: select l.loc_code, l.LOC_DESC, d.distance from cm_std_locations l, (select s.distance from...

I see - I'll experiment. I think I'll be able to use another table rather than all_objects. Thanks again, Hazel

Perfect, that gives me exactly what I wanted. It seems to be quite slow to run, but that's OK as it's just for small amounts of data. Thanks very much Stefan. Hazel

Hi, I have a simple query that retrieves appointments from a table. Some appointments are over multiple days; for these the field app_date_end is not null. select a.app_date, a.app_date_end, a.app_id from m_calendar.appointments a where a.APP_DATE_END is not null; Output: APP_DATE...

Hi Carp, thanks very much for your response. Unfortunately that still didn't work, but I've managed to find a solution which does work! I think it's quite long-winded and is probably not very good from a performance point of view, but here it is: --First part of query select u.emp_num...

Hi Carp, thanks very much for your suggestion, it looks very close to what I think I need! I haven't managed to get it to work yet; it still returns the same results as my first try. E.g. if I run it on some test data which should return one row (which is a result of the second half of the...

Hi guys, I have a union query in my stored procedure as follows, which inserts the results into a database over a database link. insert into zcmintranet@hrtest( zcmi_con_no, zcmi_car_reg, zcmi_occ_no, zcmi_car_sch, zcmi_car_cc, zcmi_mls_ntx, zcmi_mls_tax ) --Part one of the query: select...

Excellent, that worked! Also the Bcell.Copy method was a lot more efficient than what I was using. Thanks very much. Hazel