Query

Question

Post reply

Query

immaduddinahmed

Ten Centuries

Points: 1375
More actions
November 4, 2013 at 11:52 pm

#149991

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

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

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]

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 3

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-----------------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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

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;-)

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 5

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