SQL & PL/SQL :: How To Find Nearest Integer / Where Mod Returns 0

May 24, 2012

I want to find nearest integer value where mod returns 0 in sql statement. I've tried following but it doesn't fulfill my requirement.

My Try

SQL> select
2 ((1200*1000)+45-mod((1200*1000),45)) f1,
3 mod( ((1200*1000)+45-mod((1200*1000),45)),45 ) f2,
4 ((1200*1000)+45-mod((1200*1000),45))/1000 f3
5 from dual;

F1 F2 F3
---------- ---------- ----------
1200015 0 1200.015

In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.

View 20 Replies


ADVERTISEMENT

SQL & PL/SQL :: Creating Sequence - Select Returns Integer

Oct 18, 2010

im creating sequences for an already created db and need to set MINVALUE to sequences.

create sequence tb_1
minvalue X
start with X;

so, X == select Max(id_tb1) from tb_1.

I need that select returns an integer.

How can I do this??

View 11 Replies View Related

SQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data

Jun 1, 2011

Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.

Miss
Mississ
Mississipp

I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)

If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.

create table names (name varchar2(20));

insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;

View 2 Replies View Related

PL/SQL :: SQL For Rounding To Nearest Value

Oct 3, 2012

My Oracle DB version is 10g Release2 and also 11gR1

I have data like below, I need to write a SQL for the below requirement.

ID   RANGE_LOW                RANGE_HIGH
--    ------------------     -------------------
1      50                    55             
2      55                    60
3      60                    63 
4      63                    77 
5      77                    84  

The requirement is like I need to check a value between the above range low and high then round it to the nearest value.

Say, for example if I have 68 then the value is rounded to 63 else if 74 then 77 else if its in the middle of two values (70) then rounded to the highest value, here its 77.

View 4 Replies View Related

PL/SQL :: Round The Number To Nearest 10k

Nov 1, 2012

I am facing small issue, i need to round the number to the nearest 10k for some finance reporting

ex
1. if number is 2250 round to 10,000
2. if number is 5500 round to 10,000
3. if number is 4912345 round to 4920000
etc..

the number should always round up to nearest 10k..

I am using round function to achieve this..

case 1:-
SQL> Select round(2250,-4) From dual;

ROUND(2250,-4)
--------------
             0

I need 10,000 here..

case 2:-
SQL> Select round(5500 ,-4) From dual;

ROUND(5500,-4)
--------------
         10000

SQL>

This is working fine...

case 3:-
SQL> Select round(4912345 ,-4) From dual;

ROUND(4912345,-4)
-----------------
          4910000

This should round UP to 4920000

let me know if there is any way i can achieve this...

View 10 Replies View Related

Semantic Technologies :: Orageo Nearest Neighbor No Results?

Oct 13, 2013

 I am using Oracle 12c Spatial and Graph and I would like to retrieve the nearest neighbors of a point from a dataset encoded according to GeoSPARQL.I have created a spatial index on the datatype geo:wktLiteral and I to the query so the optimizer pick a plan using the index RDF_V$GEO_IDX. The query I pose is the following: 

SELECT geo, wkt
FROM TABLE(SEM_MATCH('
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
SELECT ?geo ?wkt
    
[code]...

 I do not get any error but I do not get any results neither. However, there are a lot of points around POINT(22.39 38.25) in my dataset and if I remove the filter I get results.Do I use orageo:nearestNeighbor in a wrong way?  

View 6 Replies View Related

SQL & PL/SQL :: Next Multiple Of 10 Value For Given Integer

Sep 10, 2013

Is there any function which return the next multiples of 10 value directly ?

Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50

View 18 Replies View Related

SQL & PL/SQL :: Integer As Decimal

Feb 18, 2010

I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.

How do you declare a field in Oracle as a true integer data type?

View 5 Replies View Related

SQL & PL/SQL :: How To Get Integer Portion From Decimal

Feb 21, 2012

Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.

I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands

Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);

But here is the real issue. In colomn d I only need the integer portion of the division value.

For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.

I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.

View 2 Replies View Related

PL/SQL :: Convert Integer To Time

Aug 14, 2012

I have times stored as an integer in a table.

e.g.

102506 = 10:25:06
130455

How can I convert this to time format HH24:MI:SS?

View 6 Replies View Related

SQL & PL/SQL :: Optimizing Function Returning Integer?

Feb 5, 2012

I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.

The function is working but I wanted to take an expert opinion if it can be optimized.

CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL
(I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)

[Code]....

In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.

CURSOR C_AGREEMENT
IS
SELECT AGREEMENT_SERIAL
FROM VW_AGR_AGREEMENT
WHERE NUMBER0 = I_NUMBER0
AND TYPE_ID = I_TYPE_ID
-->and sub_number is null;
-->and sumb_number = s_sub_number

View 11 Replies View Related

Performance Tuning :: ORA-02017 / Integer Value Required

Nov 28, 2012

Recent events at work are forcing me to take a much closer look at hash joins in an attempt to understand them much deeper than just on the surface. But my question today is maybe simple. I have done lots of reading and can't for the life of me figure out how to get more memory to my HASH JOINS.

is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use?

1) my databases are all using workarea_size_policy=AUTO

2) I am not afraid to go back to =MANUAL and set my own work area sizes.

3) It seems I cannot set HASH_AREA_SIZE to more that about 2GB.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
18:40:31 SQL> alter session set hash_area_size = 6000000000 ;
alter session set hash_area_size = 6000000000
*
ERROR at line 1:
ORA-02017: integer value required

I know there is a limit of about 2GB on my box for HASH_AREA_SIZE and setting it to 2GB works fine. But it is still not enough.

18:50:22 SQL> alter session set hash_area_size = 2147483647;
Session altered.
Elapsed: 00:00:00.23

is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use? Using hash_area_size and 2GB, I get better performance than with my current PGA_AGGREGATE_TARGET doing the allocation for me.

I think I'd like to get as much as 20GB to specific sessions for hash joins but maybe I am pipe dreaming?

NAME TYPE VALUE
------------------------------------ ----------- -------------------
_pga_max_size big integer 1258280K
pga_aggregate_target big integer 6G

View 5 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related

SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes

Jun 14, 2012

How to get Time Difference between two DateTime Columns in Oracle 10g ?

View 10 Replies View Related

SQL & PL/SQL :: Why Greatest Returns Null As Greatest Value While Max Returns 1

Aug 4, 2010

SQL> select greatest ( 1000,null) from dual;

GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;

MAX(A)
----------
1

Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?

View 17 Replies View Related

Semantic Technologies :: Maximum Node ID Larger Than Java Maximum Integer Value

Mar 7, 2013

I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:

-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....

when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.

Did I miss something when I created my network?

View 11 Replies View Related

SQL & PL/SQL :: Update Returns More Than One Row?

Oct 26, 2010

I have to update a table by getting values from two other tables. While doing that the inner query returns more than one value. I am not sure how to implement the logic without returning more than one row in sub query.

My query:
update buf_office_str o
set o.manager_ident =
(select sp.ident
from se2_r_src_sourceperson sp ,
(select distinct director_name, team_name from buf_sales_dump )t
where SP.SRCNAME = upper(substr(t.director_name,instr(t.director_name,' ')+1,length(t.director_name))||', '||substr(t.director_name,1,instr(t.director_name,' ')-1 ) )
and o.office_descr = t.team_name
)

Basically the query gets the manager id from sp table where sp.srcname = t.team-name.
The office_descr should be equal to the team_name.

This is the logic I am working towards:

For each office, i get the office_descr and get corresponding team_name. Match the team's director_name (from table t) with the sp.name and return the employee's id (sp.ident) for that office_descr. I need to update all 50 offices with corresponding managerid for that office in buf_office_str table.

Is it possible to get done in one update? Or, does this need a plsql proc to do this logic.

View 3 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Query Returns More Than One Row?

Oct 3, 2010

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

View 16 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related

PL/SQL :: Single Row Query Returns More Than One Row?

Mar 12, 2013

this is my sql=
" SELECT 

hpn.id AS id,
hpn.psn_id,
hpn.last_name,

[Code].....

"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,

but it returns error single row query returns more than one row, because one people can have more that one card_number.

View 9 Replies View Related

PL/SQL :: Single Sub Row Query Returns More Than 1 Row?

Apr 19, 2013

I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.

I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'

Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:

PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..

So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?

I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?

For reference, here is the query.

update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')

View 5 Replies View Related

ERROR - Single-row Subquery Returns More Than One?

Apr 19, 2011

I am really new to oraclesql and I'm trying to run a simple update statement and I get the: ERROR single-row subquery returns more than one row:

UPDATE EMP_TYP_T A
SET A.ACTV_IND = (SELECT B.ISONBOARD FROM
TMPUSERS B
WHERE B.emp_id = A.emp_id);

View 4 Replies View Related

Error - Single Row Subquery Returns More Than One Row

Jul 28, 2003

What i'm trying to do is count the number of rows those sub-queries return.

As i understand it, that error happens when more than 1 row is returned.

Here is my query:

SQL> SELECT
2 a.ssg_nme,
3 b.sftw_prod_nme,
4 COUNT((SELECT db_srvr_id FROM inv.db_srvr_t WHERE node_id = (SELECT node_id FROM inv.node_t WH
ERE node_loc_cde IN (5, 7, 8)))),
5 COUNT((SELECT db_srvr_id from inv.db_srvr_t WHERE row_status_cde = 2)),
6 COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
7 FROM
8 inv.cde_ssg_t a, inv.sftw_prod_t b, inv.db_srvr_t c, inv.appl_t d, inv.db_t e
9 WHERE d.appl_id = e.appl_id
10 AND e.db_srvr_id = c.db_srvr_id
11 AND d.appl_ssg_cde IN (6, 9)
12 GROUP BY b.sftw_prod_nme, a.ssg_nme;
COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
*
ERROR at line 6: ORA-01427: single-row subquery returns more than one row

View 3 Replies View Related

SQL & PL/SQL :: Result If A Subquery Returns No Data?

Feb 26, 2010

I have a query that can return values or not:

If it returns a value, I want to print the value returned;
If it returns no value, I would like to return a string like 'NO_DATA'.

I have already tried several things, and the last one is:
==================================================================
Select NVL2(dnh.DiaNoHableData, dnh.DiaNoHableData, 'NO_DATA')
From (Select Dia_No_Habil || '#' ||
Trim(Descripcion) DiaNoHableData
From Dias_no_Habiles
Where Dia_No_Habil = To_Char(To_Date('20080704', 'YYYYMMDD'), 'YYYYMMDD')) dnh;
==================================================================

The subquery returns no data, so the first query should print 'NO_DATA', but it returns nothing.

View 3 Replies View Related

SQL & PL/SQL :: Function Returns Searched String?

Nov 6, 2010

I just think about write a function with gives me a searched string from table.

select col1, col2 from the_table

col1 col2
--------------------------
SMS yes
Melodies no
Java build
...

And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:

select the_function('SMS') from dual;
Result: yes

how to write it?

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved