-
November 26th, 2008, 03:22 AM
#1
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?
-
November 26th, 2008, 12:18 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|