Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: M. Tanveer Ul Hassan Shaheen
  Where is M. Tanveer Ul Hassan Shaheen?
 Rahim Yar Khan
 Pakistan
 M. Tanveer Ul Hassan Shaheen
 Tags
Subject: RE: how to query
Thread ID: 311014 Message ID: 311026 # Views: 42 # Ratings: 2
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, June 28, 2011 10:31:53 AM         
   


> >
> > Hi Tanveer,
> >
> > In general, SQL requires primary and foreign key values, so the data you posted are not a "relational database" table in a SQL sense, those two rows do not seem to be related to each other, no "key" value in common, right?
> > If there are however actually only exactly two rows in that pseudo table, you can do something like this to get the desired result:
> >
> > SELECT T1.ac_code, T1.dr_amount, T2.ac_code As accountOfCrAmount ;
> > FROM aTable T1, aTable T2 ;
> > WHERE T1.dr_amount > 0 AND T2.cr_amount > 0
> > 

> >
> >
> >
> > hth
> > -Stefan
> Helo Mr. Stefan
> thanks for reply. i have only one table and two records of same voucher no. every row has diferent account no and same amount both sides. now i want to combine into one row that show.
>
> cr_acno---dr_acno----vouch_no-----amount
>
> Thanks & Regards
>
> Tanveer Ul hassan

Probably you mean something like this:

Create Cursor xx (voucherno i, accode c(20), drAmount Y, crAmount Y)
Insert Into xx Values (1, "1-02-02-0014",	500000, 0)
Insert Into xx Values (1, "5-03-04-0110", 0,	50000)
Insert Into xx Values (2, "X-02-02-0014",	500000, 0)
Insert Into xx Values (3, "Y-03-04-0110", 0,	50000)

Select Cast(Nvl(dr.voucherno, cr.voucherno) As Int) As voucherno, ;
  dr.accode As drCode, dr.drAmount, ;
  cr.accode As crCode, cr.crAmount ;
  FROM ;
  (Select voucherno, accode, drAmount From xx Where drAmount > 0) dr ;
  FULL Join ;
  (Select voucherno, accode, crAmount From xx Where crAmount > 0) cr ;
  ON dr.voucherno = cr.voucherno


Cetin Basoz

.Net has got better.Think about moving - check my blog:
My Blog
Blog (mirror) - sounds to be down

ENTIRE THREAD

how to query Posted by M. Tanveer Ul Hassan Shaheen @ 6/28/2011 9:11:00 AM
RE: how to query Posted by Stefan Wuebbe @ 6/28/2011 9:38:52 AM
RE: how to query Posted by M. Tanveer Ul Hassan Shaheen @ 6/28/2011 9:57:08 AM
RE: how to query Posted by Stefan Wuebbe @ 6/28/2011 10:09:59 AM
RE: how to query Posted by Cetin Basoz @ 6/28/2011 10:31:53 AM