Post reply

help about case

  • January 15, 2008 at 6:24 am

    #202348

    Hi. I wrote this sql as below.

    But it doesn't work as I want.

    I want to filter by with dbo.fDetermineStartDate return value when

    fDetermineEndDate is null.

    if fDetermineEndDate is not null I want to filter by with fDetermineEndDate

    Kind regards.

    SELECT Library_LibraryRecordDetails.Name AS Materyal, _Enum_TimingObjectStatus.caption AS Durumu,

    dbo.fDetermineStartDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueStartDate, Library_LoanTransfers.ActualStartDate)

    AS Baslangic_Tarihi, dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate,

    Library_LoanTransfers.ActualEndDate) AS Bitis_Tarihi,

    (CASE dbo._Enum_TimingObjectStatus.value WHEN 5 THEN _Enum_LoanTransferReturnType.caption ELSE NULL END) AS İade_Şekli

    FROM Library_LoanTransfers INNER JOIN

    Common_Accounts ON Library_LoanTransfers.MemberId = Common_Accounts.OId INNER JOIN

    Library_LibraryRecordDetails ON Library_LoanTransfers.MateriyalId = Library_LibraryRecordDetails.OId INNER JOIN

    _Enum_TimingObjectStatus ON Library_LoanTransfers.TimingObjectStatus = _Enum_TimingObjectStatus.value INNER JOIN

    _Enum_LoanTransferReturnType ON Library_LoanTransfers.ReturnType = _Enum_LoanTransferReturnType.value

    WHERE (Library_LoanTransfers.MemberId = @Uye) AND

    ((CASE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value,Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate) WHEN NULL

    THEN dbo.fDetermineStartDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueStartDate, Library_LoanTransfers.ActualStartDate)

    ELSE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate) END)

    BETWEEN CONVERT(datetime, @IlkTarih, 103) AND CONVERT(datetime, @SonTarih, 103)) AND (_Enum_TimingObjectStatus.value = @Durumu)

    ORDER BY İade_Şekli, Durumu, Baslangic_Tarihi

  • January 15, 2008 at 6:31 am

    #768083

    Try COALESCE or ISNULL.

    John

  • January 15, 2008 at 6:35 am

    #768084

    Here's your problem

    aysegul (1/15/2008)[/b

    CASE dbo.fDetermineEndDate(...) WHEN NULL

    Case <column> when null will never return true, because nothing is = to null.

    what you're probably looking for is

    Case when dbo.fDetermineEndDate(...) IS NULL THEN ....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 15, 2008 at 6:51 am

    #768093

    Thank you very much .

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply