Hi all
I'm using VB6.want to display data from two tables in my database in a grid ,here is a sample of my tables and what i want the grid to be as:
acc_cat table:
acat---------------acattitle
110 ----------------- Cash & bank
120------------------ Trading receivable
140------------------ Inventory
150----------------- Assets
151----------------- Advance
acc_chart table
acat------------ -acode----------------aTitle
110----------- --- 1101----------------- cash in hand
110---------------1102----------------- cash in bank
110----------------1103------------------main office cash
120----------------1201-------------------QIB
120----------------1202-------------------RFL
120----------------1203-------------------EMcO
140----------------1402-------------------raw material
140------------- --1403-------------------goods in transit
151----------------1513-------------------advance on salary
151-------- -------1514-----------------advance to employee
I want to display the above info as follows
110---------------cash & bank
1101--------------cash on hand
1102--------------cash in bank
120 ---------------trading receivable
1201--------------QIB
1202--------------RFL
1203--------------EMcO
140---------------- Inventory
1402----------------raw material
1403---------------good in transit
150---------------- assets
151-----------------advance
1513---------------advance on salary
1514---------------advance to employee
My database is Oracle 9i, I got a help from a friend he gave sql statement to get the output as i want ,it worked fine in SQL prompt but when i put the SQL query in recordset.open argument it seems cannot understand something !!
here is the sql statemnt i got from my freind:
select nvl(acode,acat) code,acattitle
from (select acat, null acode, acattitle
from acc_cat
where exists (select null
from acc_chart
where acc_cat.acat=acc_chart.acat)
union all
select a.acat, b.acode, b.atitle
from acc_cat a, acc_chart b
where a.acat=b.acat)
order by acat,acode nulls first;
any help ??!!
thanks