Bob - If you would like to send me (privately at mailto:\\[EMAIL PROTECTED]) a sample database and a copy of the SELECT statements you are trying, I'd be happy to help if I can.
Sami ----------------------------------------------------------- Sami Aaron Software Management Specialists 19312 W 63rd Terr Shawnee KS 66218 913-915-1971 http://www.softwaremgmt.com ----- Original Message ----- From: "Bob Thompson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 07, 2002 11:25 AM Subject: RE: For SQL (View) Wizards > Sami- > Thank you for the suggestion. I tried the statement on a table with > only four rows of data in it, expecting the last row to be returned by the > SELECT statement. Interestingly, I received an error message - > > "Error - Too many rows returned! 2441" > > Thank you for the suggestion and I will keep trying. I am not very > versed in complex SQL SELECT statements and appreciate all > the tips! > > -----Original Message----- > From: Sami Aaron [SMTP:[EMAIL PROTECTED]] > Sent: Friday, June 07, 2002 6:59 AM > To: R:BASE List Server > Subject: Re: For SQL (View) Wizards > > Try this - > > SELECT customer,ticket,amount FROM tablename > WHERE ticket >= (select ticket from tablename > GROUP BY customer,ticket > HAVING SUM(amount) > 0) > > Sami Aaron > Software Management Specialists > 19312 W 63rd Terr > Shawnee KS 66218 > 913-915-1971 Fax 913-962-9431 > mailto:[EMAIL PROTECTED] > > ----- Original Message ----- > From: "Bob Thompson" <[EMAIL PROTECTED]> > To: "RBASE - L LIST (E-mail)" <[EMAIL PROTECTED]> > Sent: Thursday, June 06, 2002 4:59 PM > Subject: For SQL (View) Wizards > > > > I have a scenario that perhaps you all could assist with > > on using a view instead of a DECLARE CURSOR routine. > > Any help would be greatly appreciated if possible! > > > > A table has columns among others, (TICKET#), (CUST#) and > > (INV_AMT). The (INV_AMT) column contains both charges > > and payments with payments being a negative number and > > charges being positive. > > > > When a customer makes a purchase of $100, there is an > > entry into the table for TICKET#, CUST# and $100. When > > they make a payment, an entry of Cust# and $-100 is > > entered and the balance is therefore (0). > > > > The question is: Can a view be created that will show all > > transactions back to a zero balance? The example below > > is data for one CUST# only: > > > > TICKET# INV_AMT (Actual running balance) > > 1 $100 $100 > > 2 $150 $250 > > Payment $100 $150 > > Payment $150 $0 > > 3 $100 $100 > > 4 $75 $175 > > Payment $75 $100 > > > > I want the view to show : > > > > TICKET # INV_AMT > > 3 $100 > > 4 $75 > > Payment $75 > > > > This may sound a little strange, but I have a requirement > > where I need to show transactions on an ongoing basis > > back to where customer accounts have a zero balance. > > Unlike more standard systems where you close a month out > > and simply carry a balance forward, this report must show > > all transactions back to a "zero balance point" and this > > point can change daily. I believe it would be much more > > efficient if a view could handle this rather than using a > > DECLARE CURSOR routine, but I have been unable > > to come up with one so far. > > > > Thank you all for your consideration! > > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > > > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
