Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2009
    Posts
    27

    Question Unanswered: Please help to find mistake in this simple query

    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

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    If you are using a stored proc then you may have to try this


    if((select count(*) from sysibm.systables where type='T' and name='VIVEK')=1)
    then
    alter table dbo.vivek drop column hai
    end if
    Last edited by nick.ncs; 01-24-09 at 03:46.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Jan 2009
    Posts
    27
    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

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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...
    IBM Certified Database Associate, DB2 9 for LUW

  5. #5
    Join Date
    Jan 2009
    Posts
    27
    Ok Sir,
    ThanKs for your help.

  6. #6
    Join Date
    Jan 2009
    Posts
    27
    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try without "fetch first row only"
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2009
    Posts
    27
    I need to fetch first row in that . i want to know how to use with the fetch ststement

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as doc indicates : fetch first rw is not supported with views
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by vivek.vivek
    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
    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.
    Last edited by MarkhamDBA; 01-26-09 at 12:05.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •