Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: sybase!!! sqlserver!!!

    I have written the below query recently.

    create table #YTD
    (
    sID varchar(20),
    Stage varchar(30),
    year_start datetime,
    year_end datetime,
    Jan decimal(16,8) null,
    Feb decimal(16,8) null,
    Mar decimal(16,8) null,
    Apr decimal(16,8) null,
    May decimal(16,8) null,
    Jun decimal(16,8) null,
    Jul decimal(16,8) null,
    Aug decimal(16,8) null,
    Sep decimal(16,8) null,
    Oct decimal(16,8) null,
    Nov decimal(16,8) null,
    Dec decimal(16,8) null,
    MTD_Date datetime,
    MTD_return decimal(16,8) null,
    YTD_return decimal(16,8) null
    )

    GO

    Insert into #YTD (sID,Stage,year_start,year_end,MTD_Date)
    select 'TP001','P','01/01/2009','24/apr/2009','24/apr/2009'

    go

    create table #Perf
    (
    sID varchar(20),
    Stage char(1),
    PerfDate datetime,
    GrossValue decimal(16,8)
    )

    go

    insert into #Perf
    select 'TP001','P','31/Jan/2009',5
    union all
    select 'TP001','P','28/Feb/2009',4
    union all
    select 'TP001','P','31/Mar/2009',7


    declare @yy int
    select @yy = datepart(yy,'01/01/2009')

    Update #YTD
    set Jan = case when datepart(mm,PerfDate) = 1
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Feb = case when datepart(mm,PerfDate) = 2
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Mar = case when datepart(mm,PerfDate) = 3
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Apr = case when datepart(mm,PerfDate) = 4
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    May = case when datepart(mm,PerfDate) = 5
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Jun = case when datepart(mm,PerfDate) = 6
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Jul = case when datepart(mm,PerfDate) = 7
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Aug = case when datepart(mm,PerfDate) = 8
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Sep = case when datepart(mm,PerfDate) = 9
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Oct = case when datepart(mm,PerfDate) = 10
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Nov = case when datepart(mm,PerfDate) = 11
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end,
    Dec = case when datepart(mm,PerfDate) = 12
    and datepart(yy,PerfDate) = @yy
    then isnull(GrossValue,0) end
    from #Perf
    where #YTD.sID = #Perf.sID
    and #YTD.Stage = #Perf.Stage
    and PerfDate between '01/01/2009' and '24/apr/2009'

    select * from #YTD

    I think there is some bug in the code. But apart from that, i have identified one new thing...Yes! I have executed the same code in sybase12.5.4 and sql2000.

    Please refer the attached document.

    I am very eager to know,

    How Sybase handle this Update statement?
    and
    How Sqlserver handle this Update statement?

    Inputs are highly appreciable!
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Instead of using a .doc attachment can you maybe use .txt and .jpg
    I simply don't trust a .doc

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Instead of using a .doc attachment can you maybe use .txt and .jpg
    I simply don't trust a .doc
    Ok.

    Did you try my code? Any idea?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What pdreyer is trying to tell you is that a DOC file can be dangerous to open, and few if any of us will take the risk of opening one from a stranger. If you'd like us to look at your output, you probably need to convert the file to a safer format such as TXT instead of DOC.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your join return more than one row
    Nothing prevent you from issuing an update command that updates a single row more than once.
    if an update statement modifies the same row twice, the second update is not based on the new values from the first update but on the original values. The results are unpredictable, since they depend on the order of processing.

    Basically you need to decide which row should be used for the update
    You are effectively doing
    Code:
    create table #t1 (c1 int, c2 char(3))
    insert into #t1 select
    1,'aa' union all select
    1,'bb'
    
    create table #t2 (c1 int, c2 char(3))
    insert into #t2 select
    1,'cc'
    
    update #t2 set c2=t1.c2 
    from #t1 t1 join #t2 t2
      on t1.c1=t2.c1
    
    select * from #t2
    
    drop table #t1
    drop table #t2

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe you need to give us more detail on what you are trying to do
    From your other posts at www.sqlservercentral.com
    Maybe all you need is a crosstab query?
    Code:
    select sID, 'Year'=year(PerfDate)
    , 'Jan'=sum(case when datepart(mm,PerfDate) = 1 then GrossValue else 0 end)
    , 'Feb'=sum(case when datepart(mm,PerfDate) = 2 then GrossValue else 0 end)
    , 'Mar'=sum(case when datepart(mm,PerfDate) = 3 then GrossValue else 0 end)
    from #Perf
    group by sID, year(PerfDate)
    Last edited by pdreyer; 04-29-09 at 10:25.

  7. #7
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Pdreyar,

    Yes! I applied the crosstab query.

    But my question is, why the output differs when i exeucte the same code(my earlier code...without sum() & group by ) in sqlserver and sybase?

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Please explain what you mean
    Although you are comparing two different products that might behave different I get the same result from both products.

Posting Permissions

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