SQL & PL/SQL :: Get List Of Vendors Based On Country And Campaign?

Mar 2, 2010

I want to get a list of vendors based on country and campaign.I first get all vendors based on country then i subtract the vendors i shouldn´t see (the vendors i should not see minus those i should see).Below you see my query - is there a better way to get the same result?

3 tables
vendor - vendorInformation
vendorcountry_spec - A vendor can be located in different countries with different information.
vendorcountry_campaign - In a country there are different campaigns. If there is a row in this table, only those with the campaignID should see the vendor else everyone in that country.

The query:

SELECT v.vendorID
FROM vendor v, vendorcountry_spec vs
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'
AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1
AND v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'
GROUP BY v.sortNr, v.vendorID

[code]....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed

Sep 17, 2010

Table A

Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo

Im looking for a query which returns results based on both city and country passed.

If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5

i.e. If country/city combination exists in DB return that row Else city row should be returned.

View 5 Replies View Related

SQL & PL/SQL :: Find Count Of Customers Which Are In Same Campaign

Apr 16, 2013

The communication table has around 1 billion rows which is partitioned by a date column which is not used in the query. The hierarchy table has only 10000 rows. rewrite the query if its possible.Its trying to find the count of customers[distinct] which are in the same campaign.

select h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_name,h.campaign_desc,h.subject, count(distinct cc.customer_id)
from communication cc,hierarchy h
where cc.campaign_hierarchy_id = h.campaign_hierarchy_id
and h.brand_cd in ('ABY','AYO')
AND H.CAMPAIGN_OWNER = 'WONDER'
and h.campaign_start_dt >'1-JUL-2011'
group by h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_desc,h.subject;

View 1 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 :: Automatic Row Fetch Based On Select List

Oct 27, 2013

db11gxe , apex 4.0 , firefox 24. I want to do automatic row fetch when the value of a select list changes ?but first i should ofcourse create a tabular form to fetch the data into it , but what i want is ,if i fetch 2 rows then the report contains only two rows , if i fetch 3 , the report contains only 3, if i fetch nothing , the report has nothing ? 

View 0 Replies View Related

Application Express :: Tabular Form (select List Query Based LOV)

Jul 10, 2012

I have this situation:

a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.

View 3 Replies View Related

Application Express :: Auto Populate Phone Number Based On Select List In Tabular Form

Dec 27, 2012

Ive a requirement which ive overlooked for a while now as below,

On my tabular form i have fields: Empid as Select List and when the value is picked from this Select List EmpID i want the associated/corresponding phone# displayed in its TEXT FIELD in that same row,

apex 4.1.1/Oracle 11gR2,

also i tried the below as a workaround but it doesnt fulfill my requirement, [URL]....

View 1 Replies View Related

SQL & PL/SQL :: How To Get Time Zone Of The Country Entered

May 24, 2010

I have a procedure wherein i'll only get country name as input parameter, how can i get Time Zone of the country entered.

View 12 Replies View Related

Application Express :: Country NOT Found In Anycharts Maps With 4.2

Nov 7, 2012

how hard it or even if it is possible to add countries to the list of available for display in the maps build-able by APEX 4.2? We have a set of data to display by country and found that Serbia is NOT in the set of associated mapable countries..Should I try asking Anychart too? Thought Hilary would pickup on this since It involves charting in APEX...

Running on Apex 4.2, Oracle 10.2, Solaris with http server.

View 19 Replies View Related

Forms :: Display Country Name And Region At Time Of Navigation

Feb 10, 2011

I have developed customer entry form, its working fine. I have 2 display items on form. One is country name & other is region, these are fetched based on country id LOV.

Now I want to display country name & region at time of navigation. I have written following code on WHEN_NEW_RECORD_INSTANCE of block.

******
DECLARE
CCNTRY_ID VARCHAR2(2);
CCNTRY_NAM VARCHAR2(25);
CREGION VARCHAR2(11);
begin
select cntry_nam,region into ccntry_nam,cregion from cntry where cntry_id=ccntry_id;

exception
when no_data_found then
message('NO DATA FOUND');
end;
**********

I always get message not data found on navigation. Problem is only with the display item other, values of database columns are being shown.

View 3 Replies View Related

Count Number Of Combinations Of Country / State Invalid For Driving Licenses

May 21, 2012

I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.

select COUNTRY_CODE, STATE_CODE, count(*) from
(select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE
where DRIVING_LICENCE is not null
and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
group by COUNTRY_CODE, STATE_CODE

The output is okay...for example I get these results:

INTINT
NZSI
NZINT
AUSTINT
NZNSW
NZ <null>

However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?

View 1 Replies View Related

Forms :: Filter Hard Coded Values In List Item (Tlist) Based On Value Entered In Text Item

May 22, 2010

I have 2 items in my form:

1) Text Item
2) Tlist

Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .

View 1 Replies View Related

Forms :: How To Avoid (no List Elements Defined For List Item) Error

Sep 27, 2011

I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".

I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.

View 1 Replies View Related

List Products List Of Client Grouped By Type Of Product?

Dec 14, 2011

Im trying to list the products list of a client grouped by type of the product. Ex:

product type

prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour

I want to list something as:

Acid

Prod.A
Prod.C

Cleaner

prod.D

Flavour

prod.B
prod.E

View 1 Replies View Related

Forms :: FRM-30351 / No List Elements Defined For List Item

Oct 30, 2011

DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;

FRM-30351: No list elements defined for list item.

List LETTER_GROUP_ID

View 4 Replies View Related

SQL & PL/SQL :: List All Tables Connected To Each Other With Constraints And List All Together

Apr 22, 2013

I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.

View 2 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

SQL & PL/SQL :: Select Whole Row Based On Max Value?

May 10, 2013

Suppose I have the following, what is the best way to select the whole row that has the highest (IDX+LEN desc, LEN desc)

IDIDXLEN
5109153
7273118
9165356

I currently have made the following two method, which work but I don't particularly care for.

WITH bob AS
(SELECT 9165 id, 35 idx, 6 len FROM DUAL
UNION
SELECT 5109 id, 15 idx, 3 len FROM DUAL
UNION
SELECT 7273 id, 11 idx, 8 len FROM DUAL)

[code]....

View 3 Replies View Related

Max Row Based On Value In Column?

Mar 3, 2011

How to get the max row based on a value in a column

View 1 Replies View Related

Sessions Based On SQL ID

Aug 31, 2012

One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?

View 9 Replies View Related

PL/SQL :: Sum Based On Group 2

Nov 26, 2012

I have the following table and data , I have six employees and carton named A,B,C,D,E and F.

Drop table a ;
Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9));
Insert into a values(1,1,’A’,10);
Insert into a values(2,1,’B’,20);
Insert into a values(3,1,’D’,25);
Insert into a values(4,1,’E’,15);
[code].......

I have also Third Query: the first and second query already solved in this link . Sum based on group

it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on

More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2

After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on

Output will be like this
Empid       Total

1 with 2    2050
1 with 3    200
1 with 4    500
1 with 5    2250
1 with 6    700
2 with 3     0
2 with 4    700
2 with 5    4300
2 with 6    1700
3 with 4      0
3 with 5    400
3 with 6     0
4 with 5    400
4 with 6    200
5 with 6    1900

View 2 Replies View Related

List All Process Description?

Feb 9, 2012

are there a table/view that lists all process with description like the DICT does for the data dictionary views?

I wanna know for example the description of J001 or Q001 process for example.

View 3 Replies View Related

List ALL AUDIT Activate?

Jan 6, 2012

I need to list all AUDIT command issued.I'm using the DBA_STMT_AUDIT_OPTS view, but it doesn't list all of them.

For example if a run this command:audit create session by my_user;

the DBA_STMT_AUDIT_OPTS will list it. But if I run this command:audit select on my_user.my_table;

The DBA_STMT_AUDIT_OPTS list only the first.

Why the DBA_STMT_AUDIT_OPTS doesn't list the second audit command?How can I track all the AUDIT commands issued?

View 4 Replies View Related

How To Display Hierarchy List

Oct 1, 2009

query in order to display a hierarchy list. Example:

Database has three tables

Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content

order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.

I assumed there was no need to track child content.

Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.

Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3

Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?

View 11 Replies View Related

SQL & PL/SQL :: List All Procedures Running On DB?

May 4, 2010

we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared

Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103

[code]...

Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.

View 17 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 :: How To List All Tables Containing Column Name

Aug 4, 2011

How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'

View 8 Replies View Related

SQL & PL/SQL :: Updating List In A File?

Mar 28, 2011

I have a file list containing some accounts and respective value which I need to deduct from their promo account and update thier new promo account with the new value greater than zero and update to zero if the value is less than zero.

fileA
0112345 11636
01233224350
0122331 43885
0155582 13825
01334423339

View 4 Replies View Related

SQL & PL/SQL :: Assigning Numbers To The List?

Apr 10, 2012

acheive the below result.

Select * from result;

res
===
3
3
3
3
3
0
3
3
3
0
3
0

output:

I would like to get res1 using SQL query.

resres1

31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....

View 9 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







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