Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Don't Know Where to Start Post Reply Post New Topic
Author Message
middy
Newbie
Newbie


Joined: 03 Jun 2011
Online Status: Offline
Posts: 2
Quote middy Replybullet Topic: Don't Know Where to Start
    Posted: 03 Jun 2011 at 4:52am
Here's my problem:

I have a table of "order lines" (lines on an invoice) and a table of "orders". The order-lines table is related to the orders table via an order-id column (1 order has many order-lines). The order-lines table has a component-code column to keep track of the type of product ordered on that line.

I need to generate a report that lists ALL of the order-lines from each order that has at least one order-line with a component code of '49-003'.

I've written many reports, but this one has me stumped! I don't even know where to start. It seems like it should be easy, but I can't quite wrap my brain around it.
IP IP Logged
Dewald
Groupie
Groupie
Avatar

Joined: 02 Jun 2011
Location: South Africa
Online Status: Offline
Posts: 47
Quote Dewald Replybullet Posted: 03 Jun 2011 at 5:01am
You cannot use a select expert for this one. If you do it will filter out all the other lines.
2 way for this to work.
 
1. Create a view or Stored Procedure in SQL to handle this.
2. Select all the orders and surpress anything that has a count of '49-003' less that 1
 
Dewald Botha
http://www.ITClarity.co.za
IP IP Logged
middy
Newbie
Newbie


Joined: 03 Jun 2011
Online Status: Offline
Posts: 2
Quote middy Replybullet Posted: 03 Jun 2011 at 5:06am
Thanks Dewald. I suspected as much.

1. Could you give me some hints how this can be done in SQL?
2. How do I count the '49-003's?
IP IP Logged
Dewald
Groupie
Groupie
Avatar

Joined: 02 Jun 2011
Location: South Africa
Online Status: Offline
Posts: 47
Quote Dewald Replybullet Posted: 03 Jun 2011 at 6:33am
You would need to create a Stored Procedure.
 
You shoudl have an inner query that accepts a parameter (the component code). In the inner query you would select the order number for the component.Then you would join the inner query to an outer query on the order number. Thereby selecting all the order lines for all orders that has this component.
 
Hope this is crear enough.
 
Dewald Botha
http://www.ITClarity.co.za
IP IP Logged
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Posted: 08 Jun 2011 at 3:16am
If you are using a Command for the sql you could do it using 'exists'
Something like
 
select order_id, order_line fields from orders O join order_lines OL
on order_id (or whatever)
where exists (select 1 from order_lines OL1 where code = '49-003'
and OL1.order_id = O.order_id)
 
I'm doing this off the top of my head,  that is probably not the right syntax but it might give you the idea.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.