StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query can't get desired output
text
Body
copied!<p>When I use this query,</p> <pre><code>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; </code></pre> <p>It gives output lik this:</p> <pre><code>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 </code></pre> <p>But I want output to look like this:</p> <pre><code>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 </code></pre> <p>Where the stamp value should print only one time for each receipt num > 5000.</p> <p>(*A single receipt may contain one or more trx_num*)</p> <p>Please help me with this. </p> <pre><code>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 </code></pre> <p>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</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload