Skip to Content

Query related issue

Hi Xperts

Below Query is working fine for Last Financial Year I got report but for Current Financial year I didn't get report the error say No Data was Found as a Result of the Selection Criteria

declare @todate as datetime
declare @enddate as datetime 
SELECT @todate= min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate='[%0]' 
SELECT @enddate= max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate='[%1]' 
select DISTINCT a.Docnum as "SAP Invoice No" ,a.docdate AS " SAP Invoice Date" ,a.Numatcard AS "Vendor Bill No" ,a.U_Date AS " Vendor Bill Date" ,a.cardname as "Name of the Supplier" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin No], (SELECT SUM(Quantity) FROM PCH1 where docentry =a.docentry)as "Quantity" ,
(select sum(LineTotal) from PCH1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM PCH4 where docentry = a.docentry and statype=-90) as "BED" ,
(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=-60) as "Cess" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=7) as "HeCess" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=1) as "VAT" ,
(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=4) as "CST" ,
 A.VatSum as "Total Tax" ,(select sum(linetotal) from PCH3 where docentry = a.docentry) as 'Freight' , 
A.DocTotal as "Net Value" 
FROM OPCH A left OUTER JOIN PCH3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN PCH4 C ON A.DOCENTRY = C.DOCENTRY left outer join PCH3 h on A.DocEntry = h.DocEntry left outer join PCH1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and g.itemcode=d.itemcode ORDER BY A.DOCNUM

Please Clarify

Thanks and Regards

Balaji

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Apr 21, 2011 at 07:26 AM

    Hi Balaji,

    This query may only work for 2007 version and lower. What is your B1 version?

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 21, 2011 at 07:14 AM

    Hi Balaji........

    The query is working fine with my DB....

    Please be sure you are selection Dates with proper DDMMYY format...

    Regards,

    Rahul

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.