Skip to Main Content

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!

Finding a closest value from lookup list

user626688Apr 1 2021

Hi, I have a requirement to find an ID from a lookup table based on the closest value. I have a main table and a lookup table. Based on the value from the main table, the query should return an ID of the closest value from the lookup table.
create table measure_tbl(ID number, measure_val number (3,2));
insert into measure_tbl values(1,0.24);
insert into measure_tbl values(2,0.5);
insert into measure_tbl values(3,0.14);
insert into measure_tbl values(4,0.68);
commit;
create table Nom_val_lkp(LKP_ID number, nom_val number(3,2));
insert into Nom_val_lkp values(1,0.1);
insert into Nom_val_lkp values(2,0.2);
insert into Nom_val_lkp values(3,0.3);
insert into Nom_val_lkp values(4,0.4);
insert into Nom_val_lkp values(5,0.5);
insert into Nom_val_lkp values(6,0.6);
insert into Nom_val_lkp values(7,0.7);
insert into Nom_val_lkp values(8,0.8);
insert into Nom_val_lkp values(9,0.9);
commit;
The result should look like:
res.PNGHow can I form a query to find a closest match of the measure value from the lookup table and pick the relevant ID in a query. Please help.

This post has been answered by mathguy on Apr 2 2021
Jump to Answer

Comments

Hadar Paiss

Hi,
There are few issues in the upgrade that might arise concerning logins:
with default install oracle 19 password are case sensitive
The password 'format' changed. You can check in dba_users
You might need to add to sqlnet.ora the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
look into https://docs.oracle.com/en/database/oracle/oracle-database/18/spmsu/finding-and-resetting-user-passwords-10g-password-version.html#GUID-D7B09DFE-F55D-449A-8F8A-174D89936304
It will cover some of it.
Regards,
Hadar

User_YAOJZ

Thanks Hadar. The password issue I knew about. I added the code to SQLNET.ORA in both the oracle_home/network/admin folder and the instantclient/network/admin folder and now get an ORA-12504 error.
I added both a tnsnames.ora and listener.ora files to both folders (with appropriate settings) but can't get past the 12504 error.
Any ideas? Greatly appreciated!
tnsnames.ora (orcltest is name of database, brian-surface4 is name of PC)
LISTENER_ORCLTEST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Brian-Surface4)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Brian-Surface4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcltest)
)
)
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oracle\WINDOWS.X64_193000_db_home)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Brian-Surface4)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

User_B9CRX

Just to clarify, do you mean you upgraded the database the app is still at Legacy 11g app client? You are testing this where the database and client are on the same host?

1 - 3

Post Details

Added on Apr 1 2021
8 comments
3,614 views