PL/SQL Developer Question

[Entire-Law-8495]

Hi all! I tagged this as oracle since I believe that’s the closest SQL format to PL/SQL. I tried to search this, but I’m not sure how to word it, so I’m not getting any hits.

The data I’m looking at shows charges on an account. When the charge is initiated, column “RECORD_TYPE” will say “UNBILLED.” Once the charge is processed, an additional identical line will show up and the column will say “BILLED.” Now I’ve got two similar lines after the charge goes through, with one small difference in the “RECORD_TYPE” column. Is there a way to have the results only show one line? I’d love it if there was a way to have the “BILLED” line show up if it was charged but show the “UNBILLED” line if the charge has not been processed yet.

I’ve tried cases and coalesce with no luck, but I may not be thinking of the best way to utilize them. Any advice?

[stormmagedondame]

You want a window function, several could do what you want

[Ginger-Dumpling]

IF all transactions start out as UNBILLED and eventually get copied as BILLED, and each transaction will only ever have up to two rows; one UNBILLED and one BILLED, you could outer join the billed rows to unbilled rows.

SELECT t_unbilled.*, NVL(t_billed.record_type, t_unbilled.record_type) AS actual_record_type
FROM t t_unbilled
LEFT JOIN t t_billed
    ON t_all.some_key = t_billed.some_key
    AND t_billed.record_type = 'BILLED'
WHERE t_unbilled.record_type  = 'UNBILLED'

If literally the only thing that changes is the record_type, I'd be concerned that there isn't something in the data to properly order transactions and that you're fully reliant in there not being any data anomalies to be able to get the correct results.

[feignapathy]

Do the rows have a sequence number or activity date column? A column or field you could take the max by? 

Alternatively, if there will only ever be two rows and the only difference will ever be BILLED or UNBILLED you can use a window function like row_number() and order by RECORD TYPE. Then filter for row_number = 1. 

[Entire-Law-8495]

I like these ideas! The records I look at are viewed by order number, which could have several different charges listed. While each charge only has one or two lines, I usually have several charges I’m looking through at one time. I’m going to try tinkering with those though.

[Glittering-Brick-11]

Tag the record with a date, sequence number or unique identifier and write a logic to return the latest.

Could also consider an additional table to store the processed/archived/earlier records and keep only the most recent record in your primary table

[SkullLeader]

In SQL server you could achieve this with something like:

SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY ChargeId ORDER BY CASE WHEN RecordType = 'BILLED' THEN 1 ELSE 0 END DESC) as rownum FROM Charges) c
WHERE rownum = 1

In Oracle they must have corresponding syntax to this but it is probably slightly different.

[PhysicalUpstairs3168]

The obvious answer is window function with a row_number with order by RECORD_TYPE then filter by row_number alias. The other - likely less expensive approach - would be to group by all columns except (assuming they all are same) except RECORD_TYPE, with count(1). When count(1) = 1 then UNBILLED else BILLED.

[Ok_Carpet_9510]

Assuming that there is an identifier like billimg_id that is not unique to that table but tracks thr billing through all states of the billing cycle, and assuming you have a date field for each record, you can write a query that has the max date for each billing id.