SQL Help

Man of Honour
Joined
17 Feb 2003
Posts
29,636
Location
Chelmsford
I have a query that outputs the following

Client, Deal Sequence, Balance
1000,1,345.56
1001,1,300.00
1001,2,300.00
1001,2,300.00
1002,1,345.00



Code:
SELECT

D.CIF_NO,
D.SERIAL_NO,
(select sum(-1 * (CASE ACCOUNT_GROUP WHEN 'CALL' THEN CV_AVAIL_BAL ELSE 0 END))  from  ACCOUNTS A where D.CIF_NO = A.CIF_SUB_NO ) as Balance

from
    DEALS D

I'd like to change the query so that the balance only appear on the first occurrence where there is more than 1 record per client

so the example above should look like this:

Client, Deal Sequence, Balance
1000,1,345.56
1001,1,300.00
1001,2,0
1001,2,0
1002,1,345.00

I'm a bit stuck on how I can achieve this tbh.

Any help would be appreciated.

Thanks
 
Man of Honour
Joined
26 Dec 2003
Posts
30,837
Location
Shropshire
What version of SQL are you using?

Off the top of my head if using MS SQL > 2012 IIRC then you could look at using the ROW_NUMBER window function and only display the balance where Row Number = 1
 
Man of Honour
OP
Joined
17 Feb 2003
Posts
29,636
Location
Chelmsford
Kind of related to the above, I have another one sorry, I'm fairly new to SQL

I'd like to read through the following table, updating the BALANCE to zero where a client (CIF_NO) previously has a balance in the table. A balance should only be shown for the most highest amount of days overdue for each client..

P9bdquO.jpg

If you look at CIF - 10001461, the top entry (serial No. 10471) correctly has a balance of 17.85 because it it's the oldest overdue, but subsequent balances for the same client should be updated with a zero.

In the example above, the highlighted balanced should be zero.

any ideas on how I can achieve that, please?


Thanks
 
Man of Honour
OP
Joined
17 Feb 2003
Posts
29,636
Location
Chelmsford
thank biily .. looks good that but I get a syntax error near the keyword TOP.?

UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
WHERE DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = CIF_NO ORDER BY b.PAYMENT_DATE ASC)

I know you can't have alias on the update table so changed to this:


UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
FROM T_QIB_MIDEALS_OVERDUE a
WHERE a.DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = a.CIF_NO ORDER BY b.PAYMENT_DATE ASC)

But i still get the same syntax error.

thank you

edit possibly the order by isn't allowed in the subquery?
 
Last edited:
Associate
Joined
31 Dec 2010
Posts
2,427
Location
Sussex
thank biily .. looks good that but I get a syntax error near the keyword TOP.?

UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
WHERE DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = CIF_NO ORDER BY b.PAYMENT_DATE ASC)

I know you can't have alias on the update table so changed to this:


UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
FROM T_QIB_MIDEALS_OVERDUE a
WHERE a.DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = a.CIF_NO ORDER BY b.PAYMENT_DATE ASC)

But i still get the same syntax error.

thank you

edit possibly the order by isn't allowed in the subquery?
That would have to a Sybase thing as Microsoft SQL is perfectly happy to use aliases and it is often the best syntax:
Code:
UPDATE a
SET SomeColumn = b.SomeColumn
FROM TableB b
JOIN TableA a ON a.ID = b.ID
Anyway, how about the following:
Code:
UPDATE t
SET BALANCE = 0
FROM T_QIB_MIDEALS_OVERDUE t
    CROSS APPLY (    SELECT TOP 1 CIF_NO, PAYMENT_DATE
                                FROM T_QIB_MIDEALS_OVERDUE iT
                                WHERE iT.CIV_NO = t.CIF_NO
                                ORDER BY PAYMENT_DATE  DESC) i
WHERE t.CIF_NO = i.CIF_NO
    AND t.PAYMENT_DATE <> i.PAYMENT_DATE
 
Associate
Joined
31 Dec 2010
Posts
2,427
Location
Sussex
Thanks

Not sure CROSS APPLY is available in SYBASE :(

Syntax variations can be tricky, but if this is the correct documentation it seems to be available:
http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html
and it looks the same as for Microsoft TSQL:
Code:
SELECT D.DepartmentName, HS.Name
  FROM Departments D
    CROSS APPLY (
        SELECT E.GivenName || ' ' || E.Surname
            FROM Employees E
            WHERE E.DepartmentID = D.DepartmentID AND E.Salary > 80000
    ) HS( Name );

EDIT: change the example to one which is uses the outer columns in the WHERE clause.
 
Last edited:
Back
Top Bottom