Using select query in SQL expression fields
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums
Results 1 to 2 of 2

Thread: Using select query in SQL expression fields

  1. #1
    Join Date
    Nov 2008

    Using select query in SQL expression fields

    I am using Crystal Reports 7.0. I can't use a select query in SQL expressions fields.

    Here are details of my problem

    I am having 3 tables

    itmmast(itno, itemtype, itemsize, make, qty)

    grnmast(grnno, grndate)

    grndetails(grnno, itemtype, itemsize, make, qty)

    The grnmast & grndetails are master-details table linked with grnno.

    The itmmast has records with hierarchy itemtype-->itemsize--->make

    Now, I want records ordered by itemsize. I am able to achieve it.

    I also need a field that gets data from following query.

    select sum(grndetails.qty) from grnmast, grndetails, itmmast where itmmast.itemsize=grndetails.itemsize and grnmast.grnno=grndetails.grnno and grnmast.grndate >=mydate;

    Now here 'mydate' depends on the financial year (1st April to 31st March)
    So if current month is between jan to mar then mydate will be 1st April of last year
    and if after march then mydate=1st april of current year.

    I tried using formula field but doesn't give me the right values in the field.

    Can anyone help me in working it out?

    Thanks in advance.

  2. #2
    Join Date
    Dec 2005
    Waterloo ON

    Re: Using select query in SQL expression fields

    First of all you need create one formula to get mydate. And then think about data source, it's OK to use 3 tables as datasource, but isn't it better to make a view as datasource?

    Only a suggestion.
    The difficulty is that you have no idea how difficult it is.

    .Net 3.5/VS 2008

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)