I have a table that contains a LONG datatype. I need to create a view based on that table with the DISTINCT function, which is not allowed on LONG datatypes. I figured if I can put a function to convert the LONG into a VARCHAR2 on the fly, I can get the DISTINCT working. Does anybody have a function to do that? Here is an example of the table I'm working with:
CREATE TABLE employee_dups
(emp_id NUMBER,
lname VARCHAR2,
fname VARCHAR2,
job_descr LONG
);
The view I'm trying to create is
CREATE OR REPLACE VIEW emp_nodups_vu AS
SELECT distinct *
FROM employee_dups;
If I could put a function on the job_descr column to convert it to a VARCHAR2 from within the view, I believe I can get it to work, like this:
CREATE OR REPLACE VIEW emp_nodups_vu AS
SELECT distinct
emp_id,
lname,
fname,
LONG_TO_VARCHAR2(job_descr
FROM employee_dups;
Anybody have any ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Well, here's the real issue. The base tables for this are a PeopleSoft system, where I cannot touch the base tables. I'm limited to just creating a view on the tables to get the data that I need.
You had mentioned putting it in a view that can be called at runtime. Can you expand on that? I didn't really catch the idea from your description.
Just because you can't change the application base table, doesn't mean you can't store the information in the database. You could create your own table, with the primary key from the Peoplesoft table, and put your computed value in that table. Then you can either use a view that joins the two tables on the primary key, or just use your table (depending on the query).
But, if you wanted to use a view, then you'd create a function that gets the primary key from its parameters, fetches the text from the Peoplesoft table, computes the checksum, and returns the value. I was also suggesting that since you can't easily refer to substrings of LONGs you might have to write this code in Java, or in a user-exit. However you write the code, the structure would be:
create or replace function Calc_People_Text(keyval number) return number is
...
select longvalue into buffer
from PeopleTable where ID = keyval;
... /* pseudocode to calc a value based on "clumps" of 20 chars would be */
calc :=0;
for i = 1, len(buffer) do
calc := calc + ascii(buffer(i)) * (i mod 20 + 1);
return(calc);
end;
then, to get the # distinct values:
select count(distinct Calc_People_Text(ID)) from PeopleTable;
But, you'll need to decide what algorithm you'll use to calculate a value and what language you'll use to write it. If you use the "shadow" table as I described above, you could probably use a C++ routine (CRC [cyclical redundancy checks] and checksum examples are easy to find on the web).
Good luck!
sure, it'll let you do the substringing of the LONG. It wouldn't be the basis for a TRUE distinct (returning a varchar2), since the distinction might be beyond the limts of a varchar2. And, the only reason I didn't suggest using COLUMN_VALUE_LONG in the function is because it's inelegant (calling a PROCEDURE to do substringing?) and slow. But, it might be a great way for you to test it out.
P.S. If you REALLY wanted to do a distinct, you'd have to "chunk" the returns - up to the maximum size of your LONG:
select distinct get2k_block(id,0),get2k_bl
get2k_block(id,3)
from PeoplesoftTable;
and in the function call dbms_sql.column_value_long
I really only need the DISTINCT to resolve an issue I'm having in a view that inadvertantly returns multiple rows because of its definition. I know the rows are unique, but because of the way the view is built, there are duplicate rows in there. I also know that there are no columns with more than 4000 characters in it, so having VARCHAR2(4000) in the view is fine. I wrote a function that works pretty good. I guess I answered my own question! :-) Here it is:
CREATE OR REPLACE FUNCTION SYSADM.long_to_varchar2 (caseId NUMBER)
RETURN VARCHAR2 IS
varcharVal VARCHAR2(4000);
varcharLength NUMBER;
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fetchIt PLS_INTEGER;
BEGIN
DBMS_SQL.PARSE(cur,'SELECT
DBMS_SQL.DEFINE_COLUMN_LON
fetchIt := DBMS_SQL.EXECUTE_AND_FETCH
DBMS_SQL.column_value_long
DBMS_SQL.CLOSE_CURSOR(cur)
RETURN varcharVal;
END;
/
Whether or not you award is up to you, but it didn't seem to me that you'd figured out how to get the long into a function (passing the key and requerying) until I suggested it. And sometimes a sounding board is just as valuable as a piece of code. In future, you might want to post more about your specific requirements, rather than post something general when that's not what you want.
Good luck.
I didn't mean to sound rude or petty. I do appreciate the points, but I was seeking clarity and closure as much as reward. There are many individuals who post their generic "question" in a manner that is really biased towards their pet solution rather than give the full details of their requirement. I've seen you posting trying to help some of these people, so I think you know what I mean and can appreciate the frustration this can lead to.
I'm sure we'll see each other posting on many other threads. I truly do wish you good luck.
Business Accounts
Answer for Membership
by: DrSQLPosted on 2002-02-05 at 07:38:41ID: 6779679
Since you're just asking for ideas, I'd suggest that you create a different kind of column. A checksum on the long data could be used to determine when you have different values. You would need a function that would calculate a numeric value based on a scan of the entire text (CRC, progressive clumps - sum(ASCII * Position mod 20), etc.). This would either be stored in your table as a new column(especially if it takes few seconds to run), or called from a view at run-time (with the O8i restrictions on LONGs, this would be difficult to write in PL/SQL).
The calculated value would then be used to determine uniqueness (especially for counts like: How many job_descr are there in the company?).
Good luck!