asked on

Sql Query

Hello,

I have Oracle 8i and create query as follow :
Table : A
Voucher no
Voucher Date
Account
Amount

Table : B
Account
Name

Sample Data for A table:
1,01/01/2018,9999,5
1,01/01/2018,9999,5
1,01/01/2018,9999,10
1,01/01/2018,7777,3
1,01/01/2018,7777,2
1,01/01/2018,6666,2
2,01/01/2018,6666,2
2,01/01/2018,9999,2
3,01/01/2018,7777,3

Sample Data for B table:
9999 Tax
7777 Expense
6666 Discount

output : only for vouchers has account number 9999
1,01/01/2018,9999,Tax,20
1,01/01/2018,7777,expense,5
1,01/01/2018,6666,discount,2
2,01/01/2018,9999,Tax,2
2,01/01/2018,6666,discount,2

Misha

Try this:

select Voucher,Voucher,Account,Amount from TableA 
join  tableB on tableB.Account = TableA.Account where tableB.Account = '9999'

Open in new window

I can see in your output summ of amount, but you not say something about it. In this case try this code

select Voucher,Voucher,Account,Amount,sum(tableB.Amount) from TableA 
join  tableB on tableB.Account = TableA.Account where tableB.Account = '9999'
group by tableB.Account

Open in new window

Bill Prew

Give this a try, it seemed to get what you wanted here.

SELECT a.VoucherNo,
       a.VoucherDate,
       a.Account,
       b.Name,
       SUM(a.Amount) AS Amount
  FROM TableA a
 INNER JOIN TableB b ON b.Account = a.Account
 WHERE a.Account = 9999
 GROUP BY a.VoucherNo, a.VoucherDate, a.Account, b.Name
 ORDER BY a.VoucherNo, a.VoucherDate, a.Account, b.Name;

Open in new window

»bp

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

don't forget to use bind variables for the '9999' part ...

WHERE a.Account = :account

then you won't get a hard parse every time you want a different account

Mahmoud Al Jundi

ASKER

I think inner join NA in Oracle 8i ?

Geert G

inner join syntax was only supported as of 9i

8:
https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/state21b.htm#2065648

9:
https://docs.oracle.com/cd/B10500_01/server.920/a96540/statements_103a.htm#2080352

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mahmoud Al Jundi

ASKER

none of them worked, bu I did it this way :

SELECT
a.Voucher_No,
a.Voucher_Date,
a.account,
b.name,
SUM(a.amount)
FROM tableA a,TableB B
WHERE
a.account=b.account and
exists
(select * from TableA C c where a.voucher_no=c.voucher_no and c.account=9999)
GROUP BY
a.Voucher_No,
a.Voucher_Date,
a.account,
b.name

Geert G

my bad, i thought the last column was some random number

in current day oracle:

SELECT 
a.Voucher_No,
a.Voucher_Date,
a.account,
b.name,
SUM(a.amount) over (partition by a.voucher_no, a.account) sum_account
FROM tableA a,TableB B
WHERE 
a.account=b.account

Open in new window

and it's supported in 8i
https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/function.htm#86759

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Since the account type is defined by the account number, you can eliminate the join using the decode function -
select voucher_no, voucher_date, account,
decode(account,9999,'Tax',7777,'expense',6666,'discount') acct_type, sum(amount) amount
from tablea a1 where exists
(select 1 from tablea a2
where a2.voucher_no = a1.voucher_no and a2.voucher_date = a1.voucher_date and a2.account = 9999)
group by voucher_no, voucher_date, account, decode(account,9999,'Tax',7777,'expense',6666,'discount')
order by voucher_no, account desc;

PortletPaul

IF there are just a small fixed number of accounts to names to consider then one might get a small benefit from a decode, or case expression, personally I would prefer to use a join so that the source of truth for account names is the table of account names.

Mahmoud Al Jundi

ASKER

I figure out the solution by myself and it has been used successfully ,Inner and outer are not supported by Oracle 8i.
First 2 solution will not help, third solution #a42512766 submitted after my solution.

Regards

PortletPaul

Only the syntax used to join differs between the following queries.

+----------------------------------------------+-------------------------------------+
| PortletPaul ID:42511508 2018-03-26 11:14     | m_jundi ID:42511750 2018-03-26 20:13|
+----------------------------------------------+-------------------------------------+
| SELECT                                       | SELECT                              |
|   a.VoucherNo                                |   a.Voucher_No                      |
| , a.VoucherDate                              | , a.Voucher_Date                    |
| , a.Account                                  | , a.account                         |
| , b.Name                                     | , b.name                            |
| , SUM(a.Amount) AS amount                    | , SUM(a.amount)                     |
| FROM tableA a                                | FROM tableA a , TableB b            |
| INNER JOIN tableB b ON b.Account = a.Account | WHERE a.account = b.account         |
| WHERE EXISTS (                               | AND EXISTS (                        |
|   SELECT 1                                   |   SELECT *                          |
|   FROM tableA a2                             |   FROM TableA c                     |
|   WHERE a.voucherno = a2.voucherno           |   WHERE a.voucher_no = c.voucher_no |
|   AND a2.account = '9999'                    |   AND c.account = 9999              |
| )                                            | )                                   |
| GROUP BY                                     | GROUP BY                            |
|   a.VoucherNo                                |   a.Voucher_No                      |
| , a.VoucherDate                              | , a.Voucher_Date                    |
| , a.Account                                  | , a.account                         |
| , b.Name                                     | , b.name                            |
| ORDER BY                                     |                                     |
|   a.VoucherNo                                |                                     |
| , a.VoucherDate                              |                                     |
| , a.Account                                  |                                     |
| , b.Name                                     |                                     |
| ;                                            |                                     |
+----------------------------------------------+-------------------------------------+

Open in new window

nb: "select 1" or "select *" makes no difference (functionally) , some older databases were slower if using "select *"
and was not sure if account was numeric or string (not specified)

Mahmoud Al Jundi

ASKER

Hello, Appreciate your feed back
but as I mentioned earlier, Inner Join not available in Oracle 8i !!!