Please help to find the mistake in this:

if(select count(*) from sysibm.systables where type='T' and name='VIVEK'=1)
begin
alter table dbo.vivek drop column hai
end

Showing error ; DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;if;JOIN

Thanks in Advance
Vivek

If you are using a stored proc then you may have to try this

i tried with case. I am not using a procedure here

but its showing some error: DB2 SQL error: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;case
when ;JOIN

Actually my query in oracle is like this:

declare countValue integer; begin select count(*) into countValue from user_tables where table_name=upper('[Param.1]'); if countValue=1 then alter table [Param.1] drop column [Param.2]; end if; end;

i NEED TO CONVERT THIS TO db2

As specified in your other post... you'll have to write a SP for that.... unless IBM has come up with something new in v9.5 which i don't think is the case...

Ok Sir,
ThanKs for your help.

I have one more query n creating view.

CREATE VIEW DBO.View_name AS

SELECTcol1,col2,col3 FROM DBO.Tablename

where

DBO.Tablename.paramname=’Prodt1’

AND

DBO.Tablename.stepname=’Step1’

AND

DBO.Tablename.productname=’proc1’

AND

DBO.Tablename.operation=’Oper1’

fetch first row only



This is showing: DB2 SQL error: SQLCODE: -20211, SQLSTATE: 428FJ, SQLERRMC: null, which means “428FJ: ORDER BY is not allowed in the outer full-select of a view or materialized query table” from db2 help site.

Try without "fetch first row only"

I need to fetch first row in that . i want to know how to use with the fetch ststement

as doc indicates : fetch first rw is not supported with views

Use the ROW_NUMBER OLAP function to find the first row according to the criteria that define row order. Because... as you wrote it, DB2 has no clue which row you want to get in case there are duplicates. Thus, the semantics of the query are not clear at all.

I am not sure what you are trying to do, but looks like part

select count(*) from sysibm.systables where type='T' and name='VIVEK'

is missing schema name - there could be more than 1 table with the same name and different schemas. Your query might find tables with different than 'dbo' schema and assumes that tables 'dbo.vivek' exists which is not correct.