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
m_jundiAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
If you only want vouchers that contain reference to account 9999 then i suggest a where clause using exists e.g.

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 EXISTS (
      SELECT 1
      FROM tableA a2
      WHERE a.voucherno = a2.voucherno
      AND a2.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

It is also possible to use IN(...) but I suspect that with Oracle 8i that exists would be more efficient (but it is a guess). To use IN(...) instead:
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.VoucherNo IN (
      SELECT a2.VoucherNo
      FROM tableA a2
      WHERE a2.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

0
 
MishaProgrammerCommented:
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

0
 
Bill PrewCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Geert GOracle dbaCommented:
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
0
 
m_jundiAuthor Commented:
I think inner join NA in Oracle 8i ?
0
 
Geert GOracle dbaCommented:
i guess none of the statements given above will work as they aren't supported in 8i

if you want an inner join with the "normal" oracle syntax:

select a.*, b.name
from TableA a, tableB b 
where b.Account = a.Account 
  and b.Account = :account

Open in new window


in sqlplus:
var account number
begin :account := 9999; end;
/

select a.*, b.name
from TableA a, tableB b 
where b.Account = a.Account 
  and b.Account = :account

Open in new window

1
 
m_jundiAuthor Commented:
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
0
 
Geert GOracle dbaCommented:
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
0
 
PortletPaulfreelancerCommented:
Using GROUP BY is required to meet the expected result e.g. this result
| VOUCHER_NO |         VOUCHER_DATE | ACCOUNT |     NAME | SUM_ACCOUNT |
|------------|----------------------|---------|----------|-------------|
|          1 | 2018-01-01T00:00:00Z |    7777 |  Expense |           5 |
|          1 | 2018-01-01T00:00:00Z |    9999 |      Tax |          20 |
|          2 | 2018-01-01T00:00:00Z |    9999 |      Tax |           2 |
|          1 | 2018-01-01T00:00:00Z |    6666 | Discount |           2 |
|          2 | 2018-01-01T00:00:00Z |    6666 | Discount |           2 |

Open in new window

was produced by this query:
SELECT
      a.Voucher_No
    , a.Voucher_Date
    , a.account
    , b.name
    , SUM(a.amount) sum_account
FROM tableA a
   , TableB B
WHERE a.account = b.account
and exists
   (select 1 from TableA C
    where a.voucher_no = c.voucher_no 
    and c.account=9999)
GROUP BY
      a.Voucher_No
    , a.Voucher_Date
    , a.account
    , b.name
;

Open in new window

Using a windowing function returns more rows than required, e.g. this result:
| VOUCHER_NO |         VOUCHER_DATE | ACCOUNT |     NAME | SUM_ACCOUNT |
|------------|----------------------|---------|----------|-------------|
|          1 | 2018-01-01T00:00:00Z |    6666 | Discount |           2 |
|          1 | 2018-01-01T00:00:00Z |    7777 |  Expense |           5 |
|          1 | 2018-01-01T00:00:00Z |    7777 |  Expense |           5 |
|          1 | 2018-01-01T00:00:00Z |    9999 |      Tax |          20 |
|          1 | 2018-01-01T00:00:00Z |    9999 |      Tax |          20 |
|          1 | 2018-01-01T00:00:00Z |    9999 |      Tax |          20 |
|          2 | 2018-01-01T00:00:00Z |    6666 | Discount |           2 |
|          2 | 2018-01-01T00:00:00Z |    9999 |      Tax |           2 |

Open in new window

produced by this query:
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
and exists
   (select 1 from TableA C
    where a.voucher_no = c.voucher_no 
    and c.account=9999)
;

Open in new window

Note that the critical part of the question is how to exclude voucher number 3 (because it does not refer to account 9999). Which is why I proposed the use of EXISTS in the where clause:

   and exists (select 1 from TableA C where a.voucher_no = c.voucher_no and c.account=9999)

I'm sorry that I forgot that Oracle 8i doesn't support full ANSI join syntax.

sample data used:
CREATE TABLE TableA
    (Voucher_no int, Voucher_Date date, Account int, Amount int)
;

INSERT ALL 
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 9999, 5)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 9999, 5)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 9999, 10)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 7777, 3)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 7777, 2)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (1, to_date('2018-01-01','yyyy-mm-dd'), 6666, 2)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (2, to_date('2018-01-01','yyyy-mm-dd'), 6666, 2)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (2, to_date('2018-01-01','yyyy-mm-dd'), 9999, 2)
    INTO TableA (Voucher_no, Voucher_Date, Account, Amount)
         VALUES (3, to_date('2018-01-01','yyyy-mm-dd'), 7777, 3)
SELECT * FROM dual
;

CREATE TABLE TableB
    (Account int, Name varchar2(8))
;

INSERT ALL 
    INTO TableB (Account, Name)
         VALUES (9999, 'Tax')
    INTO TableB (Account, Name)
         VALUES (7777, 'Expense')
    INTO TableB (Account, Name)
         VALUES (6666, 'Discount')
SELECT * FROM dual
;

Open in new window

see both queries at work (Oracle 11g R2) here:  http://sqlfiddle.com/#!4/50086/3

NOTE:
In most cases using exists with "select *" or "select 1" or "select null" would be simple alternatives in most current versions of most rdbms systems. This wasn't always true however and "select *" in older versions of some databases was a potential performance issue. I don't recall if this is true of Oracle 8i but I prefer using "select 1" or "select null".
0
 
awking00Commented:
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;
0
 
PortletPaulfreelancerCommented:
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.
0
 
m_jundiAuthor Commented:
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
0
 
PortletPaulfreelancerCommented:
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)
0
 
m_jundiAuthor Commented:
Hello, Appreciate your feed back
but as I mentioned earlier, Inner Join not available in Oracle 8i !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.