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

Thread: Using select query in SQL expression fields

  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Using select query in SQL expression fields

    Hi,
    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
    Location
    Waterloo ON
    Posts
    545

    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center