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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 3, 2011
How to get the max row based on a value in a column
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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