PL/SQL :: How To Add Asterisks To Row When One Item In Row Is Duplicated / Show One Row
Sep 7, 2013
I totally realize that but here is what im trying to accomplish. I have a table with one column that repeats.the table looks like this.
create table testCheck (check_num varchar2(20), amount number(17,2), invh_Code varchar2(20))
and it has data like so
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('002', '100', '1234'
I would like to write a query that would give me the distinct check_num's but put an asterisk on the items that are actually duplicated.so in other words expected result in this case would look like this.
I've been able to get as far as getting the unique values but what i cant do is figure out for the life of me how to put the asterisk in there.here is my query.
with Checkquery as (
SELECT count(*) over (partition by CHECK_NUM order by CHECK_NUM ROWS UNBOUNDED PRECEDING ) thiscount, CHECK_NUM, AMOUNT, INVH_CODE
FROM TESTCHECK
group by
check_num,
AMOUNT,
INVH_CODE)
select check_num, amount,invh_Code from Checkquery where thiscount ='1';
I simply want to identify the dups put an asterisk on the record and then only select one of the records.
I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.
SQL> SELECT * FROM XYZ;
NAME FNAME ADDRESS JOB -------------- -------------------- -------------------- ----------------------------------------- Bilal Khan Wali your rehman name district abbottabad student Bilal Khan Wali your rehman name district abbottaba student Bilal Kh Wali your rehma name district abbotta studen Bilal K Wali your rehm name district abbotta studen Bilal Khan Wali your rehman name istrict abbottaba tudent lal Khan i your rehman name strict abbottaba tudent [code]....
I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.
Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.
And it is still in VALID state
SQL> set autotrace on SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574); select ri from scratch1_p where scnum='444393975'; RI SCNUM ---------- ---------- 536955574 444393975
I have now created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
Re: Report Columns duplicated when region is copied.
When I copy a region which I have created via master-detail form, the report columns are duplicated. I'm actually copying the whole page but I have had other cases where I just copying the desired region and the columns were duplicated there as well. And, of course, there is no way that I know to delete columns from the form. But I doubt because -- in at least some cases I've tried -- the columns seems to be "linked" in that a change to one copy of the column was duplicated in the other when changes were applied.
In the example (below), there are three regions on the page and the problem occurs in region 2. (I think I will likely delete the first region.) It's been agonizing trying to get the report of region 2 and the tabular formish edits of region 3 on the same page. I made this in Oracle 11.2 in our APEX 4.1 and imported it to 4.2. You can see it here:
WS apex_examples_01 demo demo Appl. 991204
Page 57 is the original page created in 4.1 and imported Page 58 is the page copied from 57 under 4.1 and imported Page 59 is the page copied from 57 under 4.2 (Same result as in 4.1.)
If you want to try the page, select DSD01 from the DSD LOV, click "Query Process" and then click the edit icon. Ignore the region 1 errors -- I'll probably delete this region. The data is region 3 is for Run Date 12-May-13.
Is there some setting that allows this region to be copied without duplicating columns?
P.S. I think I know why it happens: APEX hates me!
Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed. 102 Pencil Because the items Pencil and Marker are missed. 102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
Need to create a report based on date ranges and for this created a interactive report and two page item datepicker fields P15_fromdate and p15_todate. Report works fine with this criteria.But user wants one more field quarter(P15_quarter), When they select the quarter the range values has to get reset and as to get applied to report.
Issue here is unable to find a way to set the page range item values based on the quarter field selection
I am having trouble changing a non-displayed field when a displayed field is changed.
When a user wants to un-assign a territory they want to just null the ID field (non-database item). I then need to null the PIDM field(not visible, database item) for that one record.
I tried this, but I end up putting null on all the records instead of just the one record, which ends up un-assigning all the territories not just the one.
Is there a way to assign the null to the PIDM field associated to the ID field of that particular record.
I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4
eg: - 23115645
after masking should be - xxxx5645,
create table test1(x varchar2(12)) insert into test1 values('34567745') / insert into test1 values('438765311') / insert into test1 values('1112345') / commit
[Code].....
y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx
I tried the above update, but using substr, i am able to get this if x has 9 characters
not sure how to achieve this if x has 7, or 4 characters
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1, (select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) out where in1.empid=out.empid and in1.empid='02256' order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!
i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,
i mean whenever i run above query,order of bas_cod must be change from last.
How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.
For Example:I have the data as shown below,
Start date End date 1-Jan-10 3-Mar-10 3-May-10 5-Jul-10 1-Aug-10
I wanted to output as shown below
Start date End date 1-Jan-10 2-Mar-10 3-Mar-10 2-May-10 3-May-10 4-Jul-10 5-Jul-10 31-Jul-10 1-Aug-10 1-Aug-10
I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))
Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE
/*---*/ Select G.id_group, C.rownum id_column from (select distinct id_group from MyTable) G cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select max(id_column) from MyTable)) C /*---*/
but it shows record 7, 8 and 9 as members of group 2.
INSERT INTO tra VALUES (2, 20503, SDO_GEOMETRY (2001, NULL, SDO_POINT_TYPE (1387, 0, NULL), NULL, NULL), 23037 ) /
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT 'Enquiry' as Origin, job.job_number, enquiry.enquiry_number as Defect_or_Enquiry, job.site_code, central_site.site_name, job.priority_code, priority.priority_name, job.target_comp_date, 'Target Completion Date required' as Due_Date [code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.