CODEWITHSUNDEEP
sqloracle-databaseselect
sat33man
sat33man

Reputation: 75

SQL query can't get desired output

When I use this query,

select 
   receipt_num, trx_num, 
   (case when receipt_amount > 5000 then '1' else 'null') as stamp_value,receipt_amount 
from ra_customer_all where receipt_amount > 5000;

It gives output lik this:

receipt_num             trx_num           stamp_value     receipt_amount
   23679                sf35fd                 1              5400
   23679                sdf2424                1              5400 
   23679                rer434                 1              5400
  987444                dgd343                 1              98432
    7610                sdf23                  1              6756
    7610                dfg242                 1              6756

But I want output to look like this:

receipt_num        trx_num        stamp_value      receipt_amount
  23679            sf35fd             1                 5400
  23679            sdf2424            null              5400
  23679            rer434             null              5400
 987444            dgd343             1                 98432
   7610            sdf23              1                 6756
   7610            dfg242             null              6756

Where the stamp value should print only one time for each receipt num > 5000.

(*A single receipt may contain one or more trx_num*)

Please help me with this. 

select 
acra.attribute6 office_code,
acra.attribute5 collection_number,
acra.receipt_number instrument_number,
 acra.receipt_date collection_date,
 acra.amount collected_amount,
ac.customer_name,
rcta.trx_number ,
(case  row_number() over (partition by acra.receipt_number order by rcta.trx_number)  when acra.amount > 5000 then '1'  else 'NULL' end) stamp_value,
from
ar_cash_receipts_all acra,
ar_customers ac,
ra_customer_trx_all rcta,
ar_receivable_applications_all araa
where
acra.pay_from_customer=ac.customer_id and
acra.cash_receipt_id = araa.cash_receipt_id and
araa.applied_customer_trx_id=rcta.customer_trx_id
and acra.amount > 5000

okay,i update my join query in which i added partition by but gives error as missing keyword.Can someone edit this for desired output

Upvotes: 1

Views: 269

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30775

So you want stamp_value to be 1 for the first row in a group and NULL for all subsequent rows? Use PARTITION BY:

select 
   receipt_num, trx_num, 
   (case row_number() over (partition by receipt_num order by trx_num) 
     when 1 then 1
     else NULL
     end) stamp_value,
   receipt_amount
from ra_customer_all
where receipt_amount > 5000

This will set stamp_value to 1 for the first row (using trx_num for sorting) and NULL for all subsequent rows.

Upvotes: 3

Satinder singh
Satinder singh

Reputation: 10198

Try this

 select receipt_num,trx_num, result= 
case when receipt_amount >500 then 1 else null  end,receipt_amount from ra_customer_all

Demo http://sqlfiddle.com/#!3/f29a6/1

http://sqlfiddle.com/#!3/f29a6/2

Upvotes: 1

wnSarah-Marie Nothling
wnSarah-Marie Nothling

Reputation: 51

Can you start by also including receipt_amount in your select statement for checking purposes? It could be that your expectation of receipt_amount is incorrect, and that the script behaves correctly.

Also, your null should not be in single quotes in the case statement, you will get a string in your resultset instead of a null value, as it seems you expect.

Upvotes: 0

Related Questions