Spatial :: Select Repeating Values

Sep 7, 2012

some sample data in my point geometry table.

every POLYID has two rows with NAME value, i need to select the two rows if NAME is same for a given POLYID.

example: POLYID 4351 has same name N, then i need to select two rows with PILYID 4351.

POLYID POINTID           NAME
-----------------------------------------------------------------
4348 5763           N
4348 5764           F
4351 5741           N
4351 5756           N
4367 5721           M7

[Code]....

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

PL/SQL :: Append Values If Repeating Same Name?

Jan 4, 2013

if the same name repeating it should to append with _1 and _2 until same name reached.

select 'fname' name from dual
union all
select 'lname' name from dual
union all
select 'email' name from dual
union all
select 'fname' name from dual
union all
select 'fname' name from dualmy output should be like below...

fname
lname
email
fname_1
fname_2

View 11 Replies View Related

SQL & PL/SQL :: Mark Repeating Values As Blank

Jun 1, 2010

I have a result set with the following structure

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
A1 B1 20 50
A1 B2 30 40

But i want to restructure this result like below,

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
20
B2 30 40

I am just trying to change the repeating values on a certain combination as blank.

View 5 Replies View Related

PL/SQL :: SQL To Sum A Column While Avoiding Repeating Values

Jan 31, 2013

I have these records in a table:

CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips

My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:

CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips

That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.

View 4 Replies View Related

Spatial :: Table Containing All Values And Description Of Each?

Sep 23, 2013

for functions like SDO_GEOM.RELATE, do the return values like "COVEREDBY", are those values/definitions defined in the database somewhere?  For instance a table containing all the values and a description of each? 

View 4 Replies View Related

Spatial :: How To Get All The Values Of The SDO_ORDINATE_ARRAY Of A Oriented Point

Dec 14, 2012

I'm trying to get the values of a Geometry which is an Oriented Point. To that, i'm using SDO_UTIL.GETVERTICES, but this utility only obtains the point X,Y, it doesn't obtain the values of the orientation vector.

The Geometry is this:
MDSYS.SDO_GEOMETRY(
2001,8307,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),
MDSYS.SDO_ORDINATE_ARRAY(-75.586088632813272,6.1794352615514194,0.57278169530235967,-0.81970795380217887,0)
)

The query is this:
SELECT c.ipid, c.nombre, t.X, t.Y, t.Z, t.W
FROM Hidrantes c,
TABLE(SDO_UTIL.GETVERTICES(c.geometria)) t
where c.ipid = 4691117
ORDER BY c.ipid, t.id;

Result:
4691117          -75,5860886328133     6,17943526155142          (null) (null)

As you can see, it only obtains the X,Y values but not the values of the orientation vector, how can I get the values?

View 2 Replies View Related

Spatial :: Select Attributes In Point Cloud

Oct 10, 2012

I have data in point cloud (x,y,z,a,b,c,d (a,b,c,d are some attributes about this point)). i create a point cloud (sdo_pc - geometry), block tables (sdo_geometry - geometry). if i use a function to_geometry, result is only in multi point without attributes. it is way display this 3D data, and at the same time inquire of concerning for a attributes in concrete points?

View 0 Replies View Related

Spatial :: Convert Shape File To Oracle Spatial

Jul 31, 2012

I have a road network which is shape file format and i want to export it to oracle spatial format using any free tool, I am using arcgis 9.3.1 and Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bi,

View 2 Replies View Related

Spatial :: Error In Spatial Index

Jun 16, 2013

I created a spatial index on a table, but i get the error message:

ORA-13033: the data in sdo_elem_info_array of sdo_geometry is unavailable

How can i write a SQL command to delete those unavailable records?

View 2 Replies View Related

Spatial :: ORACLE 11g Spatial And GRS 80

Apr 4, 2013

I am working on a project that will require both projected coordinates and geographic coordinates for different product files coming in. I have no issues defining, storing, extracting or matching the geographic coordinates. My columns for those coordinates with SRID 8307 are defined in the user_sdo_geom_metadata table as follows:

INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)),8307);

the index: create index "MYDB"."INVGEO_IDX" on "MYDB"."INVENTORYGEOMETRY"("INVGEO") indextype is MDSYS.SPATIAL_INDEX;

how I am going to have a column that has both Geographic and Projected coordinates. Am I allowed to define more SDO_DIM_ELEMENT(s) fro the SDO_DIM_ARRAY with different min/max values. As an example, would I be able to still use an SRID of 8307 for both WGS 84 and GRS80 data (ellipsoid), something like the following:

INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05),
(MDSYS.SDO_DIM_ELEMENT('FIXEDGRID_.5Resolution', 0, 26916, .5)),8307);

Is that possible or do i have to create a new column in the database? Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table.

how I'm going to make this work for both the geographic and projected coordinates or if I'm going to be able to make it work.

View 4 Replies View Related

Select Into With Null Values?

Apr 20, 2007

i have a stored proc where i am selecting a value into a variable like so:

SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;

Now, my problem lies in where there is no value returned (oracle will throw an error).

View 3 Replies View Related

Select Records With Same Values Of Field?

Oct 9, 2007

there are some data in the table que_history (seqnbr is the key), e.g.

SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39

I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .

select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;

but the result is not the right.

View 3 Replies View Related

Forms :: LOV / Auto Select Values

Apr 13, 2012

I have an LOV on my form which holds a list of course units for a student to select and insert. However some units on the LOV must be COMPULSORY(not optional).... so i was wondering is there a way to have these auto selected from the LOV?

View 4 Replies View Related

SQL & PL/SQL :: Select From List Of Literal Values?

Jul 18, 2011

Is there a way to loop through a list of literal values.

For instance
create table car(
name varchar2(11),
passengers int,
price int
);

insert into car values ('fiat',1,1000);
insert into car values ('bmw',2,2500)
insert into car values ('ford',2,1500)
insert into car values ('ferrari',4,5000)

select
max(price)
from car
where passengers=1

How can i in a single query do this for where passengers = 1
then passengers = 2
then passengers = 3 etc
where i have a list of possible values for passengers.

Just to update I realise this can be done with

select
name,
max(price)
from car
where passengers in (1,2,3)
group by name

but in just wanted to know if there is a way of iterating through a literal list in tsql

View 1 Replies View Related

SQL & PL/SQL :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

How To Select Data Where Non-numeric Values Omitted

Nov 13, 2008

I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?

bubbagumpshrimp
"ORA-01722: invalid number"
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;

View 6 Replies View Related

Using Array Values In IN Clause Of Select Query?

Sep 9, 2008

I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

View 13 Replies View Related

SQL & PL/SQL :: SELECT Query For Displaying Aggregate Values?

Jul 1, 2011

i have table with following data.

yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763

I want the output in following way.tell me the select query for this

yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398

[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Select Only Records That Have Multiple Values For A Column

Nov 21, 2011

I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:

idCoupon Type
123Amount
123Percent
456Amount
789Percent

I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:

idCoupon Type
123Amount
123Percent

View 6 Replies View Related

SQL & PL/SQL :: Select Date Values From A Table Between Two Dates?

Feb 16, 2010

Oracle 10g

In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.

select update_date from table1
where update_date between sysdate and sysdate-90

The above query retrun no data even data is there in the table for this range.

Update_date

11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10

[code]...

View 7 Replies View Related

SQL & PL/SQL :: Select Numeric Values From A Varchar Column

Feb 5, 2011

select numeric values from a varchar column

For Example:

select * from t1 ;

ID
----------
00300
ABCXY
04230
xyzab

i need to fetch only numeric values from column id

My output should be

00300
04230

View 8 Replies View Related

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

PL/SQL :: Total Users Who Select Value From Q1 Group By Values

Dec 5, 2012

i have a table below; sql> desc css_survey

SURVEY_ID NOT NULL NUMBER(5)
USER_ID NOT NULL VARCHAR2(15)
ACADEMIC_SEMESTER VARCHAR2(25)
Q1 NUMBER(1)
Q2 NUMBER(1)

below are the records

survey_id user_id academic_semester q1 q2

1 1 2012 2 3
2 2 2012 2 3
3 3 2012 3 4
4 4 2012 3 4
5 6 2012 2 4

the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result

total_users question_Q1_select question_Q2_select
3 2
2 3
3 4
2 3

View 2 Replies View Related

Storing List Of Values Which Are Returned By A Select Query?

Oct 9, 2012

I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.

how to proceed further and the ways how can I write this requirement.

View 1 Replies View Related

SQL & PL/SQL :: Function To Select Column Values Separated By Comma?

May 23, 2012

I need to write a function which will take table name as input and should return all the columns separated by coma (,).

For example I have a table product as

PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001

My function should return the output as

100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001

Is there any inbuilt function available?

View 10 Replies View Related

Reports & Discoverer :: Select Query With Minimum Values

May 30, 2013

The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!

SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....

View 7 Replies View Related

PL/SQL :: Select Records Based On First N Distinct Values Of Column

Sep 25, 2012

I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.

eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10

query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10

query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..

How to write a query for this inside a loop.

View 5 Replies View Related

Reports & Discoverer :: Select List Of Values From Parameter Form?

Jun 9, 2012

I am trying to select multiple values from a parameter form based on a select statement.

I created the parameter and write the select statement under list-of-value property However what I want is to let users choose multiple values from the select statement not only one value.

View 1 Replies View Related

Forms :: How To Select 1st Record From The Duplicate Values In Table Without Using Rownum And Rowid

Apr 23, 2010

how to select 1st record from duplicate vales in a table.

If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.

but here duplicate values r there.

SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;

ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87

if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.

View 3 Replies View Related







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