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

    join to files does not work

    The two files must be sorted on the join fields so it looks like your first .DAT file needs to be sorted In order to understand recursion, you must first understand recursion.
  2. taupirho

    Compiling PRO*C on Windows 7

    I think the problem is with the bracketed term (x86) in your path. Windows 7 and Oracle 11 just don't like them. Can you show exact command you are using to precompile the code. In order to understand recursion, you must first understand recursion.
  3. taupirho

    SQL Script that only fires every bi-weekly payday

    I'm assuming this should return 1 when you're expecting it to fire. So, the IW bit gets the week number of the year which for the 4th Feb returns 05 on my system. So the mod.... part returns 1 therefore the thing you selecting returns 0 and not 1. In order to understand recursion, you must...
  4. taupirho

    Date series generation using pure SELECT - SQL?

    try this with dtes as ( select end_date - start_date diff ,start_date from ( select to_date('30-apr-15') end_date, to_date('01-jan-15') start_date from dual ) ) SELECT dtes.start_date + LEVEL the_dte FROM dual,dtes CONNECT BY LEVEL <= dtes.diff / In order to understand...
  5. taupirho

    Counting occurrences inside a table

    You need a select from ( select .... construct So lets say select date, something event, sum(count(something)) over (order by trunc(date)) as running_total from my_table returns date event result 01-09-2015 something 1 02-09-2015 other 1 10-09-2015 something 2 11-09-2015 something 3 Now...
  6. taupirho

    Tables Stats not updating

    What outputs do you get from your commands a) EXEC DBMS_STATS.gather_table_stats ... and b) ANALYZE table ... ; In order to understand recursion, you must first understand recursion.
  7. taupirho

    Tables Stats not updating

    Are you doing this via right-click on tablename -> Statistics-> Gather statistics or via SQL worksheet? Can you provide a screenshot of what you are actually running/typing in to sqldeveloper. In order to understand recursion, you must first understand recursion.
  8. taupirho

    writing stock letters from a databse

    I'm not 100% sure what you are asking here but I'll give it a shot and please correct me if I'm wrong. It sounds like you need to use the case statement select DOT , case when trunc(sysdate) - TRUNC(DOT) > 180 then 0 when trunc(sysdate) - TRUNC(DOT) between 61 and 180 then 1...
  9. taupirho

    Exclude a primary name if a preferred name is entered

    If you're on V9 use analytics instead, I think it will be faster than John's suggestion select id,type,name from ( select count(*) over(partition by id order by type) cnt,id,type,name from your_table ) where cnt = 1 In order to understand recursion, you must first understand recursion.
  10. taupirho

    cursor for loops to insert record into a table

    Things to check: 1) When you run your SQL statement in SQLPLUS SELECT EMP_ID, DEPT_ID, INSERTED, LAST_UPDATE, HIRE_DATE FROM EMPLOYEES; does it actually return any data 2) If yes to the above you need to start entering debug statements into your code use "dbms_output.put_line ..." in...
  11. taupirho

    Calculate when someone's membership is new, current or a lapsed membership per term

    I presume the data under Type is not actually stored anywhere and is just your own description of the various membership states, otherwise you would just count where type like 'NEW%' , 'Continuing%' , 'Lapsed%' etc .... If my presumption is correct what is actually stored in the table for...
  12. taupirho

    Deleting millions of records

    create new_audit_table as select the_records_you_want_to_keep from old_audit_table drop old_audit_table rename new_audit_table to old_audit_table recreate indexes etc... on old_audit_table In order to understand recursion, you must first understand recursion.
  13. taupirho

    Best type of index to use...?

    Can I just check you are using FORALL/BULK operations in your PL/SQL In order to understand recursion, you must first understand recursion.
  14. taupirho

    awk help with closing multiple named pipes

    I have never used gpfdist, but with my "programmers hat" on, this is what I would look at. First of all does the location gpfdist::localhost:9001 specified in the create external table command definitely point to /tmp on the server? Also what happens when you try and read the named pipes with...
  15. taupirho

    Finding Repeat Records within 72 Hours

    This might give you a start: SQL> select * from tom; CUST DTE ---------- --------- 1 01-JAN-15 1 02-JAN-15 1 03-JAN-15 1 07-JAN-15 1 08-JAN-15 1 15-JAN-15 1 17-JAN-15 1 19-JAN-15 1 20-JAN-15 9 rows...
  16. taupirho

    searching for files reaching file version limit

    How about something simpler like: $ del version_max.txt;* $ dir disk$1:[000000...]/select=version=minimum=30000/out=version_max.txt $ if (f$file_attributes("version_max.txt","FFB").ne.0) $ then $ write sys$output "FILE(S) WITH VERSION NUMBER > 30,000 DETECTED (32,768 MAX ALLOWED) - SEE...
  17. taupirho

    Query - Two records before High Amount and Two Records after High Amount

    This is inelegant and wont work in a lot of circumstances, but it works on your particular data set. If I had the time I could make it better but over to you now to improve and make work more generally with your data ! SQL> select * from tom; AMOUNT AM_DATE ---------- --------- 123...
  18. taupirho

    Application Sorting Dilemna - Should I Use Oracle For Sorting

    Actually change this bit ('//text()'), ',') final_str to ('//text()'), '|') final_str and that last '|' should go In order to understand recursion, you must first understand recursion.
  19. taupirho

    Application Sorting Dilemna - Should I Use Oracle For Sorting

    Try this SQL only method, the only thing you have to sort out is an extra '|' at the end of the string select rtrim (xmlagg (xmlelement (e, x || '|')).extract ('//text()'), ',') final_str from ( with str as ( select...
  20. taupirho

    Application Sorting Dilemna - Should I Use Oracle For Sorting

    The important bit of info - from a sorting point seems to be the dept no, so I guess you can just split the line into separate records on the field separator. Sort the resulting records then re-join them. You dont say which system you are on but the above would be easy if you are on UNIX...

Part and Inventory Search

Back
Top