CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Using select query in SQL expression fields

    Hi all,

    My problem is I am not able to insert a field getting data from query. I am using Crystal Report 7.0. I have tried it thru SQL expression fields.

    The scenario is, I have 3 tables

    itmmast(itemtype, itemsize, make, qtymtr)

    grnmast(grnno, grndate)

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

    grnmast & grndetails are master-details table.

    In itmmast, hierarchy is based on

    itemtype---> itemsize--->make


    In my report, I am getting records ordered by itemsize, irrespective of make. I am able to get such records.

    Now the problem is I need to insert a field in record that pulls data from grndetails.

    The query can be formalized as follows:

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

    Now mydate depends on date for financial year 1st April to 31st March.
    So if current date lies bet. jan to mar, it financial year is 1st April of previous year and if it is after march then its year will start from 1st April of same year.

    I tried using formula fields but din't get correct values.
    can anyone help me how I can work it out?

  2. #2
    Join Date
    Dec 2003
    Location
    Northern Ireland
    Posts
    1,362

    Re: Using select query in SQL expression fields

    I would create variables for working out the start and end dates of the current financial year. Is sql expression fields a part of crystal reports? (I've never used it). I usually bind a datatable to it which I create through code.

    Code:
    select 
     sum(grndetails.qty) 
     from grnmast 
     inner join grndetails on grnmast.grnno=grndetails.grnno 
     inner join itmmast on grndetails.itemsize= itmmast.itemsize 
    where grnmast.grndate between '2008-04-01' and '2009-03-31'
    Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook


    0100 1101 0110 1001 0110 0011 0110 1000 0110 0001 0110 0101 0110 1100 0010 0000 0100 0101 0110 1100 0110 1100 0110 0101 0111 0010

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
  •  





Click Here to Expand Forum to Full Width

Featured