Oracle

Help with BLOB Column

Hi Everyone,

I need your help on this query. I need to get a particular value from a column which is a BLOB datatype. Here is the sample data. The query has to get only these two values 120001(netamount) = 20($amount)

sql

insert into flag_cust
(CID,
LAST_NAME,
FIRST_NAME,
M_INIT,
ADD1,
ADD2,
CITY,
STATE,
ZIP,
DOB,
SEX,
ACCT_STATUS,
HH_INCOME,
FICA_SCORE,
NEW_IND,
SURROGATE_CID)
SELECT
MK.*
,CASE WHEN MK.new_IND = 'n'
THEN DW_SEQ.NEXTVAL
Else null
END as surrogate_cid
FROM
(SELECT
c.CID,
LAST_NAME,
FIRST_NAME,
M_INIT,
ADD1,
ADD2,
CITY,
STATE,
ZIP,
DOB,
SEX,
ACCT_STATUS,
HH_INCOME,
FICA_SCORE,
case when C.cid is null
THEN 'y' else 'n'

i want write curser to get information from diff tables with table name and what info is feed for that field

i want to get info abt consumer from database with all info like table name and consumer details

Calculate Count Of Consecutive leaves applied using Query

Hi All,
I Shall be thankful if any one suggest me in this issue.Requirement is to calculate the number of Consecutive leaves applied using query.

In PS_TL_RPTD_TIME,we have the employees applied for leave with TRCs.
We have to calculate the number of consecutive leaves applied as the count between a date range.But, in this table the saturdays and sundays are not listed.This is breaking my query.The sample output i've got is

Emplid Start_date End_date Count
11534 21-APR-10 23-APR-10 3
11534 26-APR-10 30-APR-10 5
11534 03-MAY-10 07-MAY-10 5

Oracle Query Problem

Hi All,
I was write a query to get all the ePerformance records' row count using only PSRECDEFN table.Here's my query and is ging me error as 'table or view doesnot exist'.

DECLARE
CURSOR c2 IS SELECT recname FROM psrecdefn WHERE objectownerid = 'HEP' AND RECTYPE NOT IN (1,2,3,4,5);
v_c1 c2%rowtype;
row_cnt NUMBER;

BEGIN
OPEN c2;
LOOP
FETCH c2
INTO v_c1;
EXIT
WHEN c2 % NOTFOUND;
EXECUTE IMMEDIATE 'SELECT COUNT(*) from PS_' ||v_c1.RECNAME|| 'INTO'|| row_cnt;
DBMS_OUTPUT.PUT_LINE('rows in table PS_' || v_c1.RECNAME || '=' || row_cnt);

Retrieving Tables from different databases using database links with Escape Characters

Hi!
I want to get tables which are present in one database and not present in other database using database links.The tables must start with PS_.
I wrote a query for this.but its not functioning.I want to get it using Escape characters.Can u help me out please..

SELECT C.TABLE_NAME FROM USER_TABLES C WHERE C.table_name like ‘PS\_%’ESCAPE’\’ AND C.TABLE_NAME NOT IN(SELECT A.TABLE_NAME FROM USER_TABLES A
WHERE A.table_name like ‘PS\_%’ESCAPE’\’
INTERSECT
SELECT B.TABLE_NAME FROM USER_TABLES@PJCRM B
WHERE B.table_name like ‘PS\_%’ESCAPE’\’)