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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 PrewIT / Software Engineering ConsultantCommented:
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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
PortletPaulEE Topic AdvisorCommented:
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
awking00Information Technology SpecialistCommented:
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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.