Search results for query: *

I am trying to find all users who had transactions on three different dates and I don't want to wade through a compilation of the transactions of those dates. The only way I know how to do it is with intersect: select * from transactions where transaction date = date1 intersect select * from...

Hey Targ, here you go. DROP TABLE ANNUALSTATEMENTS_TEST; create table annualstatements_test (ID INTEGER, YEARENDDATE DATE, YEAR1 DATE, SALARY1 NUMBER(9,2), YEAR2 DATE, SALARY2 NUMBER(9,2), YEAR3 DATE, SALARY3 NUMBER(9,2), YEAR4 DATE, SALARY4...

Hey Tharg, its part of our annual audits on this data before we send out the annual statement to our members who are still working, we are required by law to send this notice every year, we are currently in the middle of reviewing the audits, seeing if they can be refined at all. Part of those...

Hey tharg, I have access to the raw data, but that is data going back 40+ years and for over 70000 people, when you are looking for each person's top 4 and top 5 earning years, for each and every year, that query would take quite a while to return. Lauren Lauren McDonald, B.Sc. Programmer...

Thanks Tharg, the preliminary looks good. I was really hoping to get away without using another table. This table is an annual pension statement that is sent out to everyone, and their pension is based on a top 5 and top 4 average. So everyone has one record in this table for every calendar...

Here is some sample data: ID YEAR1 SALARY1 YEAR2 SALARY2 YEAR3 SALARY3 YEAR4 SALARY4 YEAR5 SALARY5 123 31/12/2008 45000 31/12/2007 20000 31/12/2006 4700 31/12/2005 8700 31/12/2003 17000 321 31/12/2007 40000 31/12/2005...

Hi Everyone, I have a table with the following structure EmployeeID Number Year1 Date Salary1 Number Year2 Date Salary2 Number Year3 Date Salary3 Number Year4 Date Salary4 Number Year5 Date Salary5...

Correct, it's only when I need to format the source data, or pretty it up that I even try to use the to_number function. I have been using the to_char function to get the formatting that I have needed. I've always assumed that the format mask was exactly that a mask, that was placed on the...

Correction, only when I try to use a format mask with the to_number function on a numeric column do I get the 1722 error. Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

I just tried it on a table the column is defined as number(9,2) and got the 1722 error. Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

Just a follow up, it seems to me that the 1722 error will be thrown whenever you try to use the to_number on a numeric, am I right in assuming this? Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

Thanks Tharg & Mufasa Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

Hi Group, I have this payroll table that I am just wanting to get the total number of records from, which is done using select count(*) from payroll, now I need to format the number so it is easier to read the number, the table has several million records in it. I have tried using to_number...

Yes it does, I couldn't find anything that explicitly stated it, thanks Santa. Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

This is just a hypothetic question for right now. Supose a user/schema is granted select permission to use a function, call it function1, in the DB, and suppose function1 calls other functions. Now, does the select permission of the user/schema cascade down so that the user/schema can now call...

I have been unsuccessful in converting the LONG to CLOB, I will be doing this a different and longer way. Thanks Santa and sem for your help. Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan

I'm not the DBA, so I can't migrate to CLOB. The only thing that I know about the text column is that it is a long datatype and that it holds sql code which is used generate pdf reports on the company website, so my guess is that it could run greater than 32K, but I am not sure. I will try...

here is code for the function. create or replace function long_instr(r_id in report_level_sql.id%type) return varchar2 is rsqlcode report_level_sql.text%type; Result varchar2(10); begin select rls.text into rsqlcode from report_level_sql rls where rls.id = r_id; if...

Hi Everyone, I have this function I am building so I can parse long datatypes. I keep getting a ORA-06502 error when I come to the following line select rls.text into rsqlcode from report_level_sql rls where rls.id = r_id; rls.text is the column with the long datatype, rsqlcode is declared...

Thanks BJ & Santa, I was hoping that I could get away without creating a function, but it looks like this will work. Lauren McDonald, B.Sc. Programmer Analyst Saskatchewan Healthcare Employees' Pension Plan