Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Is it not a good practice to display primary key on UI?

Badam123Mar 13 2018 — edited Mar 15 2018

Hi All

Presently we get device details though a feed where multiple devices can be mapped to single CustomerId. The device details are loaded in table : TBL_DEVICES . The devices presently do not have any natural primary key so we have created a sequence as primary key . We had a new request to assign a unique key for each device so that they can be searched from the UI using the unique key (Presently they can search using CustomerId) . The team has asked me to create a new column and increment using a sequence .

TBL_DEVICES  ( Existing structure)

-----------------

DEVICE_ID         NUMBER  PRIMARY KEY using Sequence

CUSTOMER_ID  NUMBER  FOREIGN KEY

DEVICE_DESC  VARCHAR2(100) 

CAPACITY         NUMBER

Can't we use the existing primary key as a unique key? . When I asked the same they said that showing primary key on UI is not a good practice . I was not convinced as it doesn't make sense to add a column and sequence which adds no value.

Please suggest.

Thanks

Comments

rbglossip
Try removing the null from the second query of the union. You have null aliased as DateClosed in the first but a column aliased as DateClosed in the second.
FLROOPENHEAD.DATEOPENED DateOpened,NULL DateClosed,
FLROHISTHEAD.DATEOPENED DateOpened, null, FLROHISTHEAD.DATECLOSED DateClosed,
1 person found this helpful
Umesh Gupta
Please post ur table structure and some sample data for clarifiaction..

Regards

Umi
1 person found this helpful
user639304
Hi OraclePLSQL,

In your union operation, the first select has four columns and the second select has five columns.

Hope this helps.
1 person found this helpful
Achyut K
Hi,

On comparison with 1st query and 2nd query,it can be easily noticed that ,1st query is returning 4 columns and 2nd query returns 5 columns in select clause.
In order to work union ,both of your queries should select equal no of columns.

Hence replace your query

Original query(1st ) :
SELECT FLROOPENHEAD.RONBR RepairOrder,FLROOPENHEAD.DATEOPENED DateOpened,NULL DateClosed, 
 (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
 WHERE FLREPAIRSTAT.REPAIRSTATID = FLROOPENHEAD.REPAIRSTATID) RepairOrderStatus,
....
Relplace with : 
 
SELECT FLROOPENHEAD.RONBR RepairOrder,FLROOPENHEAD.DATEOPENED DateOpened,NULL,/*Here you need missed the comma */
 DateClosed, 
 (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
 WHERE FLREPAIRSTAT.REPAIRSTATID = FLROOPENHEAD.REPAIRSTATID) RepairOrderStatus,
....
Hope this helps

Regards,
Achyut
1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 12 2018
Added on Mar 13 2018
#sql, #table
29 comments
2,445 views