Re: Concatenating and copying columns data

damn, please ignore my last post.

On Apr 11, 3:38 pm, "Usman" <khanus...@xxxxxxxxx> wrote:
I apologize for sending the below query on this forum. I am having
similar problem with SQL server version of this trigger (attached
below). Can anyone please recommend how to workaround this issue?

Usman

CREATE TRIGGER [usmank2].[process_ods_inst_id_trig] ON
usmank2.PROCESS
FOR INSERT AS

begin

DECLARE @PROC_KEYTAG VARCHAR (10)
DECLARE @PROC_PROJID VARCHAR (100)
DECLARE @PROC_ID VARCHAR (100)
DECLARE @INSTID numeric(20)

declare @start int , @len int , @asciistr varchar(8000),@onechar
CHAR(1)
declare @ctr int

SELECT @PROC_KEYTAG = KEYTAG FROM INSERTED
SELECT @PROC_PROJID = PROJ_ID FROM INSERTED

set @asciistr = ''
set @start = 0

select @ctr= count(*) from syscolumns where name='PROJ_ID' and
id=object_id('PROCESS')

if @ctr > 0
begin
set @len = len(@PROC_PROJID)
if @len > 0
while @start < @len
begin
set @start = @start + 1
set @onechar = substring(@PROC_PROJID,@start,
1)
if isnumeric(@onechar) = 1
set @asciistr = @asciistr + @onechar
else
set @asciistr = @asciistr +
ascii(@onechar)

end
end
set @asciistr = @asciistr + @PROC_KEYTAG

set @INSTID = convert (numeric(20), @asciistr)

UPDATE [PROCESS] SET ODS_INST_ID = @ctr WHERE KEYTAG = @PROC_KEYTAG

END

On Apr 11, 3:21 pm, "Usman" <khanus...@xxxxxxxxx> wrote:

I have written the following trigger, I need to check if PROJ_ID
column does not exists in table then skip the logic. How can I
accomplish that? Oracle is throwing error at :new.PROJ_ID statement,
if I remove PROJ_ID column from the table. Is there any other way to
access PROJ_ID value from the new row?. Any help in this regard will
be appreciated.

CREATE OR REPLACE TRIGGER "USMANK_PW4"."PROCESS_AFTER" BEFORE
INSERT ON "USMANK_PW4"."PROCESS" FOR EACH ROW
DECLARE
PROC_KEYTAG VARCHAR (10);
PROC_PROJID VARCHAR (100);
PROC_ID VARCHAR (100);
INSTID numeric(20);
fldCount NUMBER(5);
startpos int; len int; asciistr varchar2(8000); onechar CHAR(1);
begin
asciistr := '';
startpos := 0;
SELECT COUNT('PROJ_ID') INTO fldCount FROM ALL_TAB_COLUMNS WHERE
TABLE_NAME ='PROCESS' AND COLUMN_NAME = 'PROJ_ID';
IF (fldCount != 1) THEN
len := length(:new.PROJ_ID);
if len > 0 then
while startpos < len loop
startpos := startpos + 1;
onechar := substr(:new.PROJ_ID,startpos,1);
if onechar >= '0' and onechar <= '9' then
asciistr := asciistr || onechar;
else
asciistr := asciistr || ASCII(onechar);
end if;
end loop;
end if;
end if;
asciistr := asciistr || :new.KEYTAG;
INSTID := TO_NUMBER(asciistr);
:new.ODS_INST_ID := INSTID;
end;
On Apr 10, 5:01 am, "joel garry" <joel-ga...@xxxxxxxx> wrote:

On Apr 9, 6:33 am, "Usman" <khanus...@xxxxxxxxx> wrote:

Yes, this is the tiny part of the bigger business task. I have
mentioned the problem in best possible words. I don't have time to go
through literature and will be glad if someone suggests something to
get me going.

So, why is it are you asking this identical question in both cdos and
mpsp?

And why is it you don't have time to "go through literature?"

jg
--
@home.com is bogus.
But then again, aren't so many things?


.

Relevant Pages