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


ADVERTISEMENT

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

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

SQL & PL/SQL :: Select Length - String Literal Too Long

Jun 21, 2011

If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.

select length('string with more than 4000 char) from dual;

View 10 Replies View Related

Application Express :: How To Select MIN Value Under Default Tag Of Select List

Oct 5, 2012

I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."

BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.

BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.

THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.

View 7 Replies View Related

SQL & PL/SQL :: Get List Of Values Which Is Not The Given List?

Sep 1, 2013

I want to get the list of values which is not the given list .

I have the below data in my emp table

SQL> select emp_id from emp;

EMP_ID
----------
101
102
103
104
105
106
108
110
120
220
225

11 rows selected.

But I need to display the values from the list of values which are not in the emp table . So the result will be '3000,3002,3003'.

SQL> select emp_id from emp where emp_id ?;
EMP_ID
----------
3000
3002
3003

View 9 Replies View Related

Forms :: List Of Values

Jul 8, 2010

I have developed one sample form using oracle Internet developer Suite 10g. It has two fields.

1.all employees(I set as combobox)
2.members (i set as text box)

In runtime all employees column has all the employee name displayed . If i select the employee name one by one it added into the members textbox. How to solve that issue?

View 13 Replies View Related

SQL & PL/SQL :: Using Where Not Exists Against A List Of Values?

Jul 13, 2012

I have a list of values from a spreadsheet and want to know which values are NOT matched in columns of a table

here's the list (really 4000+ long)

1234,
2345,
3244,
and I want to find the values that are not in the table 'table_name' like this
....

where not exists (Select number_n from table_name
where number_n in ('1234', '2345', '3244', ...(the list above))

View 11 Replies View Related

SQL & PL/SQL :: Get Unique Row Number For List Of Values?

Apr 9, 2012

I need a query to get the below.

Source :

select * from test;
LVL
1
2
3
1
2
3
4

Output:

LVLSEQ
11
21
31
12
22
32
42

I need above to uniquely identify the set of data.

View 25 Replies View Related

SQL & PL/SQL :: Display All Values In Same Order As In IN List

Mar 29, 2010

I had a table main with 2 fields. please see the contents below.

seq name
a aaa
b bbb
c ccc
d ddd

1. My query is as below

select seq,name from main where seq in ('a','b','c','b','d','d') output of query is

a aaa
b bbb
c ccc
d ddd

but I need the output to be as shown below.

a aaa
b bbb
c ccc
b bbb
d ddd
d ddd

I need to display all the values in the same order as in the "IN" list.

View 18 Replies View Related

Application Express :: Select List With Value Yes And No

Mar 4, 2013

say i got 10 select list each with value yes or no

I need to fire a dynamic action so i have a variable which will show the count of select list selected which has been selected as yes or no...

How can i accomplish this is apex 4.2 db 11g ?

View 2 Replies View Related

SQL & PL/SQL :: Generate List Of Records Within Range (two Values)?

Mar 15, 2012

I am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:

Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.

I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.

How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?

View 3 Replies View Related

Forms :: Get List Values Starting With Particular Alphabet

Sep 30, 2011

I have made one LOV for vendor_names. On form level i want that when the user will enter a particular alphabet in a textbox assigned with LOV, the list should popup with only those elements starting with that particular alphabet. I have written the following code in the query of LOV wizard

query:

select distinct(bpt_ven_nm),bpt_ven_cd from bill_hdr1_tab
where bpt_ven_nm like ('enter.vendr_nm'||'%')
order by bpt_ven_nm

Wherin 'Enter' is the name of block and 'vendr_nm' is field name.

But its getting unsuccesfull. Any alternate way to get this result.

View 2 Replies View Related

Forms :: Sorting Values In List Item

Dec 15, 2010

I have two list items and from left list item values are populated to right list item through Add and Remove buttons and vice versa.My requirement is

1.) I need to sort the values of list item whenever a new element is added to the list item.

View 1 Replies View Related

Forms :: One Drop Down List Box From Which Want To Do Add Or Copy Values

Oct 28, 2013

I have one drop down list box from which i want to do add or copy the values chosen from the list to another text box and then using this copied values ,i will pass them as parameters to run report.This list is getting populated using recordgroup and then what ever user chooses , it will be added to the another text box with space or delimiter.

View 3 Replies View Related

Application Express :: Conditional List Of Values

Nov 15, 2012

Is there any way I could set the condition on the list of values.

For example if Value of the item x is null then use select ...

and when value of the item x is not null use this select ....

View 4 Replies View Related

Forms :: ORA-01007 - Variable Not In Select List

May 9, 2013

I wrote the following

DECLARE

VISCONNECTED BOOLEAN;
VCONEXAO EXEC_SQL.CONNTYPE;
VARQUIVO_SAIDA TEXT_IO.FILE_TYPE;
VCURSOR EXEC_SQL.CURSTYPE;
VCOLUMNVALUE VARCHAR2(2000);
VSTATUS PLS_INTEGER;

[code].....

But, on line "VSTATUS := EXEC_SQL.EXECUTE(VCURSOR);" i get the error (ORA-01007 - VARIABLE NOT IN SELECT LIST). Whats is wrong?

View 8 Replies View Related

SQL & PL/SQL :: Filter Each Row In A Select Statement Based Upon A List?

May 18, 2011

Is it possible to filter each row in a select statement based upon a list that you loop through. For instance in a where clause having an in statement but rather than selecting any value in the in statement looping the main select through each value in the "in" filter.

Eg:

CREATE TABLE TRANSAC(
Item_id number ,
Transaction_Date DATE,
Category_id number
)

You could then write

SELECT MAX(Transaction_Date)
from TRANSAC
where category_id = '141'

and then where category_id = '142' then '143' etc.

I understand i could use an inline view instead of a where clause but the issue is that i already have the list of values I want to compare against.

View 5 Replies View Related

Application Express :: Get Display Value From Select List

Sep 9, 2012

I am using Apex 4.1 and I have a select list in my page. I know i can access the value of the selected item by :P1_SelectList, but I would like to know how to access the display value of the selected item.

View 6 Replies View Related

SQL & PL/SQL :: List All Tables In Database With Particular Record Values Combination

Jul 1, 2013

I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.

empidenamejob
7369, 'SMITH', 'CLERK'

I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?

View 3 Replies View Related

Application Express :: How To Connect List Of Values To Regions

Aug 20, 2013

I created list of values like Bar chart,Column Chart,Pie Chart.these list of values added to the text field named as Chat type. In same region i created 3 chart sub regions. My requirement is if i select chart type-pie chart.the page will show only pie chart region only.Same to Bar chart and Column charts also. 

View 3 Replies View Related

PL/SQL :: How To Pass List Of Values In Where Clause Of Query Parameter

Sep 18, 2012

I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:

declare
TYPE crMain_record is RECORD (
v_code             dummy.v_code%type,
n_no               dummy.n_no%type,

[Code].....

END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..

And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the

View 3 Replies View Related

Forms :: Invalid Asset - Press F9 To See List Of Values?

Dec 6, 2010

invalid asset . . . press f9 to see list of values

The above message appears on the item when I call lov.

View 3 Replies View Related

Forms :: List Item Values Showing With ALT + DOWN Key Combination

Jan 9, 2013

In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.

View 1 Replies View Related

Application Express :: Matching Against A Multi-select List

Mar 8, 2013

I have a 'Select List' widget (P_FILTER) which I have set to return multiple values. In my report region, I have something like this

Select A from B where B.Col_1 IN upper(:P_FILTER)

When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?

View 2 Replies View Related

Application Express :: Enter Input In For Select List?

Nov 28, 2012

I would like to enter input in for a select list. That is, if I have a select list for the common colours (blue, green, red, ... ) that a user could query on, then I would like a user to be able to type in the colour - purple in the box next to the select list. I've looked at the select list options and couldn't see anything obvious?

View 2 Replies View Related

Application Express :: Select List Read Only / Or Display Only

Sep 3, 2012

I have Select list itemS based on Lookup tables. When the form in Edit mode, I want to prevent the end user from changing the value of these Select list items. How can I accomplish this ??? **If I change the type of the item to Display only, then it will show the returned value of the Select list.

View 1 Replies View Related

Forms :: ORACLE ERROR Unable To Read List Of Values

Sep 28, 2010

We have recently shifted our database from 10G to 11G and after the intial hickups most of the thinghs have stablised. We had changed system by Alter System command so that database does not have case sensevity problem and with that all the reports from the forams have also stabilied However some of the forms are failing with the above error ORACLE ERROR Unable to read list of values

These form are running fine with users having DBA privelege. However other users this is failing.

We have also checked that with users not having DBA privelege we are able to read the data in the table within the form also. The query in the record group is very simple

"SELECT CODE,SHORTDESC FROM GENCODES WHERE CODETYPE='BS' AND CODE <> '00'"

All these forms were fine previously when database was on 10g.

Moreover there are many other fields where list of values are there and allthose are ruing fine.

I have also checked datatype and length in query as well as fields on the form

View 5 Replies View Related

Application Express :: Currency Format For List Item Values

Sep 18, 2012

How can i apply currency format to the select list values queried from database tables. The data is stored in database in number format. The user wants to see 1000 separator commas in the list item values.

View 2 Replies View Related

SQL & PL/SQL :: Invalid Results When Including Date With Null Value In Select List?

Jun 15, 2010

I'm working on a Oracle Database, and I'm gettin incorrect results when including a date field in the select list which is NULL in the table.

This works correctly and returns exactly one row:

SELECT firstField FROM table WHERE firstField = 'value'

while this doesn't and returns no rows:

SELECT firstField, secondField FROM table WHERE firstField = 'value'

Where secondField is of type date and its value is NULL (00-000-00). Note that the only thing that changes is the select list.

View 6 Replies View Related







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