oracle - Is there a way to compare multiple rows in one column in PL/SQL? -

-

i've been using sql on 25 years, i'm relatively new oracle. previous experience has been in db2, , sql server.

i asked write script through table following layout:

orderno int
clientid varchar(20)
order_date datetime
productcode varchar(5)
creditlimit money
accountno varchar(25)

the users enter client's creditaccountno on every order. (i know should pulled table, it's homegrown application, , didn't write way. someday, i'll them let me fix that.)

the problem that, while clients have more 1 credit account, have one. users mistakenly enter wrong account no. want me write script looks @ each clientid, , display clients have multiple account numbers in table.

normally, have (pseudocode):

clientid cursor     read distinct clientid     order cursor         read order record clientid         if first iteration, set variable x = accountno         if accountno <> x print clientid, x, accountno         read next row     loop     read next row loop

i'm wondering if there isn't pl/sql command let me away second loop.

hopefully, question makes sense.

why not this?

select distinct client_id, accountno ( select client_id,         accountno,         count(distinct accountno)              on ( partition client_id) client_account_count your_table )  client_account_count > 1;

update

added number of rows each account number, suggested in comments justin cave. helps 1 distinguish account numbers correct mistakes.

select distinct client_id, accountno, cnt ( select client_id, accountno,  count(distinct accountno) on ( partition client_id) client_account_count,  count(*) on ( partition client_id, accountno) cnt your_table )  client_account_count > 1;

Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

wso2esb - How to concatenate JSON array values in WSO2 ESB? -

-
we have json object below, expected result in: "(001),(011),(089),(120)". can suggest how iterate json array , concat values mention . "(001),(011),(089),(120)" thanks in advance. { "element": { "values": { "agentid": "aaaaa", "transactiondata": [ { "no": "001" }, { "no": "011" }, { "no": "089" }, { "no": "120" } ] } } } you can using iterate mediator, filter mediator , properties operation scope. try solution. @ end have (001),(011),(089),(120) value in concat-data property. have added complete proxy reference. <?xml version="1.0" encoding=...
Read more