Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query


Query

Author
Message
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 9738
Fix your join:
left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode


Comment out the date filter and rerun the query.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search