Query

  • hello

    select vm.VoucherTypeCode,vm.VoucherNo,vm.VoucherDate,vm.Narration, vt.VoucherType

    from VoucherMaster vm

    left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode

    left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode

    where

    vm.VoucherDate BETWEEN @FromDate and @ToDate

    AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

    i am getting problem in this row AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

    when i search from voucherno its give me null result

    please help me out

    immad

  • Nobody can help you here ..we dont know your data and table definition.

    PLease post table defintion along with (sample) data to get the result.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Fix your join:

    left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode

    Comment out the date filter and rerun the query.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • my data is like this

    1st table VoucherMaster

    VoucherTypeCode---VoucherNo-----VoucherDate----Narration

    JV----------------13140001------2013-03-26-----Sale Summary Closed.

    JV----------------13140002------2013-03-26-----Sale Summary Closed.

    JV----------------13140003------2013-03-28-----Stock Transfer

    JV----------------13140004------2013-03-28-----Sale Summary Closed.

    ----------------------------------

    2nd table VoucherDetail

    VoucherTypeCode----VoucherNo

    PU-----------------13140001

    PU-----------------13140001

    PU-----------------13140001

    PU-----------------13140002

    PU-----------------13140002

    ------------------------------------

    3rd table VoucherType

    VoucherTypeCode-----VoucherType

    JV------------------ADVANCE VOUCHER

    BP------------------BANK PAYEMENT VOUCHER

    JV------------------BANK RECEIVED VOUCHER

    PU------------------Client Order

    CP------------------CASH PAYMENT VOUCHER

    thanks for the help

    immad

  • First ..fix the LEFT join as chris has asked. another thing where you are getting "NULL" results (in which column)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • i fix it

    and i just want that if i didnt give date parameter and i use voucher no parameter then result gives me result from voucher number

    ALTER procedure [dbo].[vocuher]

    @VoucherNo int,

    @VoucherType varchar(50),

    @FromDate DATETIME,

    @ToDate DATETIME

    as

    begin

    select

    vm.VoucherTypeCode,

    vt.VoucherType,

    vd.VoucherNo,

    vm.VoucherDate,

    vm.Narration

    from VoucherMaster vm

    left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode

    left join VoucherType vt on vd.VoucherTypeCode = vt.VoucherTypeCode

    where

    (ISNULL(@VoucherNo,'')='' OR vd.voucherno = @VoucherNo)

    and vm.VoucherDate BETWEEN @FromDate and @ToDate

    --and vd.voucherno = @VoucherNo

    end

    thanks for the help

    immad

Viewing 6 posts - 1 through 5 (of 5 total)

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