Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. hazelsisson

    Ref Cursor query containing IN operator as parameter

    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.
  2. hazelsisson

    Ref Cursor query containing IN operator as parameter

    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...
  3. hazelsisson

    "IN" operator - invalid number error

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

    "IN" operator - invalid number error

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

    "IN" operator - invalid number error

    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...
  6. hazelsisson

    Using TRIM function with outer join

    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...
  7. hazelsisson

    Using TRIM function with outer join

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

    Using TRIM function with outer join

    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...
  9. hazelsisson

    Help with Decode statement to replace Case

    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...
  10. hazelsisson

    Help with Decode statement to replace Case

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

    Help with Decode statement to replace Case

    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...
  12. hazelsisson

    Correlated Subquery - invalid column error

    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
  13. hazelsisson

    Correlated Subquery - invalid column error

    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...
  14. hazelsisson

    Return row for each date in range

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

    Return row for each date in range

    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
  16. hazelsisson

    Return row for each date in range

    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...
  17. hazelsisson

    Union Query question

    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...
  18. hazelsisson

    Union Query question

    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...
  19. hazelsisson

    Union Query question

    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...
  20. hazelsisson

    Excel Macro - Method 'Range' of object '_Global' failed

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

Part and Inventory Search

Back
Top