cancel
Showing results for 
Search instead for 
Did you mean: 

Query Check

former_member209725
Participant
0 Kudos

Hi Experts,

SELECT T0.NUMATCARD, T0.[DocDate], T0.[CardName],T0.[Address], T3.[TaxId0], T0.[DocTotal] FROM OPCH T0 INNER JOIN CRD7 T3 ON T0.CARDCODE = T3.CARDCODE WHERE T0.DOCTOTAL > [%0] AND T0.CARDNAME = [%1]

Above given query runs every record two time.

i.e.

DocNum, DocDate, CardName, Address, TaxId0

1, 12-02-11, ABC , Surat, India, ABCDE12345

1, 12-02-11, ABC , Surat, India, ABCDE12345

plz help.

Regards,

Ravi

Edited by: RAVI_JHA_SAP on Mar 3, 2011 5:19 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Ravi .

Try This Code :

Declare @Total as Numeric(10)

Declare @Party as NvarChar(10)

Select @Total = (Select min(T0.DocTotal) From opch T0 Where T0.Doctotal > [%0])

Select @Party = (Select DISTINCT T0.CardName From opch T0 Where T0.CardName = [%1])

if (@Party is null)

begin

SELECT T0.NumAtCard, T0.DocNum, T0.CardName, T0.Address, T1.TaxId0, T0.DocTotal

FROM OPCH T0 INNER JOIN

CRD7 T1 ON T0.CardCode = T1.CardCode WHERE T0.DocTotal > @Total

end

else if (@Party != 'ALL')

begin

SELECT T0.NumAtCard, T0.DocNum, T0.CardName, T0.Address, T1.TaxId0, T0.DocTotal

FROM OPCH T0 INNER JOIN

CRD7 T1 ON T0.CardCode = T1.CardCode WHERE T0.DocTotal > @Total And T0.CardName = @Party

end

Thanks

--

Ashish

Answers (1)

Answers (1)

former_member312729
Active Contributor
0 Kudos

Hi Ravi,

try this

SELECT Distinct T0.NUMATCARD, T0.DocDate, T0.CardName,T0.Address, T3.TaxId0, T0.DocTotal FROM OPCH T0 INNER JOIN CRD7 T3 ON T0.CARDCODE = T3.CARDCODE WHERE T0.DOCTOTAL > %0 AND T0.CARDNAME = %1

Regards:

Balaji

former_member209725
Participant
0 Kudos

Hi Balaji,

Query runs error.

Incorrect syntax near '0' 2

And 1 more thing if i select ALL in selection criteria then it should come with ALL BP Master Record.

Regards,

Ravi

Former Member
0 Kudos

Hi Ravi......

Try this.......

SELECT T0.NumAtCard, T0.DocDate, T0.CardName,T0.Address, T3.TaxId0, T0.DocTotal FROM OPCH T0 INNER JOIN CRD7 T3 ON T0.CARDCODE = T3.CARDCODE WHERE T0.DOCTOTAL > '[%0]' AND (T0.CARDNAME = '[%1]' or '[%1]'='')
Group By T0.NUMATCARD, T0.DocDate, T0.CardName,T0.Address, T3.TaxId0, T0.DocTotal

Hope this will help you.....

Regards,

Rahul

former_member209725
Participant
0 Kudos

Hi Rahul,

Query is ok.

But in some specific BP Name Records are returning two times as i said in last few posts.

Regards,