Highlighted
Respected Contributor.
Respected Contributor.
700 views

Making serial number field unique

Hi All,

I'm trying to make the Asset.SerialNo field unique to prevent any duplicate serial number from being entered in our AM database.

'SerialNo' field is already indexed but it's not UniqueOrNull, and I cannot modify it since it's a system object. I can't create another index either from App designer because the field is already indexed and is throwing an Oracle error.

I have tried putting a script in 'Validity' -

IF amDbGetLong("SELECT Count(1) FROM amAsset WHERE SerialNo = '" & [SerialNo] & "'") = 1 THEN

   Err.Raise(2010, "Error - Serial number already exists.")
   RetVal = FALSE
ELSE
   RetVal = TRUE
END IF

This prevents users from entering a duplicate serial number, but when I try to insert a new record with a serial number which is not there in database, this script is throwing an error. I have tried with condition -

IF amDbGetLong("SELECT Count(1) FROM amAsset WHERE SerialNo = '" & [SerialNo] & "'")  > 1

But this lets users enter a duplicate serial number first time, throws an error on the second try.

Please let me know where am I going wrong.

 

Thank you.

 

0 Likes
9 Replies
Highlighted
Honored Contributor.
Honored Contributor.

A couple of things...

1) Check with Support.  I have a vague recollection of this being something we had a KB article for back in the Peregrine days, but I just did a quick search and did not turn it up.  Does not mean it is not there though.

2)  Do not try and modify the existing Index.  Bad JuJu messing with System Objects.  However you should be able to create a new UniqueOrNull index on the SerialNo field.  AFAIK nothing should prevent this.  Just make sure there are no Duplicates when you go to create the Index.  Also... stronglly recommend UniqueOrNull over Unique sinece there may well be many cases where you do not want a value for SerialNo.

3) What is the Error you are getting from your VScript on Insert?

Cheers... Russ

Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

Hi Russ,

Thanks for replying. I will check with support if I absolutely have to, just thought this wouldn't be such a difficult thing to do in the first place.

2) I am getting this error - "ORA-01408: such column list already indexed" when I try to create a UniqueOrNull index on SerialNo field. An OOB index on SerialNo field already exists, and like you mentioned no tampering with system objects.

3) With this condition provided below, if I try to insert a completely new record with a serial number not in our database - 

IF amDbGetLong("SELECT Count(1) FROM amAsset WHERE SerialNo = '" & [SerialNo] & "'") = 1 THEN

I am getting the custom error message from the script I have created - "Error - Serial number already exists."..

 

This is the part which is confusing me most, when I execute this statement -

SELECT Count(1) FROM amAsset WHERE SerialNo = 'XYZ' from SQL developer, I get a count 0. Does 'amDbGetLong' take a ceil value by any chance? If not, how come Count(1) of a completely new SerialNo throw the custom error message?

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

An update on point 3. I am getting the below error no matter what I do. Initially I thought I was getting an error while inserting, but that's changed now -

2018/08/21 06:06:25.291    5    1    [Thrd#:3592](12007) The serial number already exists in the database. ('Line 9 of script ''Validity' of table 'Assets (amAsset)''')
Even if I change a model record, business value etc. I am getting the same error.

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

You are receiving the error no matter the change because you are not excluding blank serial number records from the logic.  You also do not want to include the record being inserted as part of the count.

IF amDbGetLong("SELECT Count(1) FROM amAsset WHERE lAstId <> " & [lAstId] & " AND SerialNo <> '' AND SerialNo = '" & [SerialNo] & "'") <> 0 THEN
   Err.Raise(2010, "Error - Serial number already exists.")
   RetVal = FALSE
ELSE
   RetVal = TRUE
END IF

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Now that I think about it more, you might have to wrap the following around main logic...

IF [SerialNo] <> "" THEN
END IF

This will ignore validity processing of any context record (ie. existing/inserting) when no value exists.

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Interesting.  This would seem to be an Oracle vs. MSSQL thing since MSSQL will allow the "redundant" index.  It has been several years since I have had a client using an Oracle backend.  Perhaps ask your DBS if there is a way to get Oracle to allow it?

At this point your best bet is likelly to have Support bless a change to the System Index.  You can keep going with teh Validity Script... or adding an On Insert Workflow... or .... but these are all getting increasinglly convoluted and intensive.  You want the RDBMS to do the work here.

As a rule of thumb you can generally get more restrictive, just not less.  i.e. making SerialNo unique will not be a problem whereas removing the unique contraint on AssetTag would be.... problematic.

If yout really want to avoid talking to Support you can backend it and you or your DBA can just drop the existing Index and recreate it.  If you name it the same then you should be OK.  Not the path I would recommend, but.... Also... the UniqueOrNull "indexes" are implemented (if memort serves) as Triggers not true Indexes which might complicate things for you.

Cheers... Russ

Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

Hi Mark,

I modified the code to this -

IF [SerialNo] <> "" THEN

   IF (AmDbGetLong("SELECT Count(1) FROM amAsset WHERE lAstId <> " & [lAstId] & " AND SerialNo = '" & [SerialNo] & "'")<>0) THEN
       Err.Raise(2010, "Error - Serial number already exists.")
       RetVal = FALSE
   
   ELSE

      RetVal = TRUE

   END IF
END IF

but still got the same error - "Serial number already exists".

So, I checked the ADBLOG for more details. Whenever I am updating a record or any software linked to the record, here's what I found in the logs -

2018/08/21 12:57:14.786    15    32    [Thrd#:6344]Cluster Feature Inheritance (Table: 'amAsset')
2018/08/21 12:57:14.786    12    32    [Thrd#:6344]CalcFieldStatus
2018/08/21 12:57:14.786    13    32    [Thrd#:6344]Query - Get
2018/08/21 12:57:14.786    14    32    [Thrd#:6344]GetPhysicalField
2018/08/21 12:57:14.786    15    64    [Thrd#:6344]Exec: SELECT Count(1) FROM amAsset A1 WHERE A1.lAstId <> :1 AND A1.SerialNo = :2
2018/08/21 12:57:14.786    15    32    [Thrd#:6344]Binding
2018/08/21 12:57:14.786    16    32    [Thrd#:6344]Bind Param(1) = 77902808
2018/08/21 12:57:14.786    16    32    [Thrd#:6344]Bind Param(2) = 'MXQ3030157'
2018/08/21 12:57:14.802    15    128    [Thrd#:6344]VGet: 0.016s
2018/08/21 12:57:14.786    12    256    [Thrd#:6344]Eval: 'Validity' of table 'Assets (amAsset)'
2018/08/21 12:57:14.802    13    256    [Thrd#:6344]Error loggued 154551520 - -53 Error - Serial number already exists. ('Line 11 of ''Validity' of table 'Assets (amAsset)''')
2018/08/21 12:57:14.802    13    256    [Thrd#:6344]Error loggued 154551520 - 12001 Error - Serial number already exists. ('Line 11 of ''Validity' of table 'Assets (amAsset)''')

As per my understanding, the 5th line is simply executing the select query, but not evaluating the IF condition from the script. Any thoughts on this?

Thanks.

 

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

This should not be.  1 - Make sure the script logic is on the 'Assets (amAsset)' table and no other table.  2 - Change "Err.Raise(2010" to "Err.Raise(2009".  3 - After saving database structure change, stop then start the APM service (do not re-start).

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

This likelly is not the problem, but I never use the "Count(1)" syntax... I generally use the PK of the table... so in your case I would use "Count(lAstId)"... nothing else jumps out at me though... so it might be worth trying even though AFAIK both should produce the same result.

Also... for MArks #3 I think he meant stop the APM before making the DBB change and then start it again after.  When making DBB changes you really should have no other connections to the database.  Even if just changing Meta Data and not the actual Schema.  Just play it safe.

Cheers...  Russ

Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.