what I want my code to do is display the customers who live in either Georgia or Florida who recently placed an order totaling more than $80.
My code so far:
SELECT LastName, FirstName, SUM(Retail * Quantity) AS Total From CUSTOMERS, ORDERS, ORDERITEMS, BOOKS Where (State = 'FL' OR State = 'GA') Group by customers.customer# Having SUM(Retail * Quantity) > 80.00;
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..
SELECT d.event_code_dt, count(d.event_number) count1 FROM main_event a,sec_event d WHERE a.event_id = d.event_id(+) GROUP BY d.event_code_dt ORDER BY d.event_code_dt, count(d.event_number) desc
Results are like this
11-MAY-10 21-MAY-10 22-MAY-10 NULL
And I want results to be like this: NULL 11-MAY-10 21-MAY-10 22-MAY-10
Same thing happens with Event_month NULL Feb 2009 Aug 2009
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.
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?
I am not sure if this is even possible however I will give it a try here. is there a way to take a value from an In List that doesn't exist in the table and get SQL PLus to output that value with an assigned decode construct. example.
1 select decode(count(object_name),1,'EXIST',null,'NOT EXIST',0,'NOT EXIST'), 2 object_name, object_type 3 from dba_objects where object_name in ( 4 'GV_$METRICNAME', 5 'GV$METRICNAME', 6 'GV_$METRICGROUP', 7 'GV$METRICGROUP', 8 'ABC' 9 ) 10* group by object_name, object_type SQL> /
I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
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.
i am using List Manager Item .i have use List of values (lov)
SELECT LD.LOOKUP_NAME d ,LD.ID r FROM CRM_LOOKUP_MAS LM, CRM_LOOKUP_DTL LD WHERE LM.LOOKUP_ID=LD.LOOKUP_ID AND LM.LOOKUP_TYPE='TRAVEL RHYTHM' and LD.ACTIVE_FLAG='Y' order by LD.LOOKUP_NAME }
when i click to add new value and click then pop up window open with LD.LOOKUP_NAME display value but when i click on add Button then value added in to below box with LD.ID
i want to display here my LD.LOOKUP_NAME value into below box.
I've gotten it to display the percentage of markup correctly(as in, multiplied by 100 with no decimal places), but I cannot for the life of me get the percentage sign to display on the right of the values(i.e. 40, but not 40%).
Here's my code so far:
SELECT title, to_char(round((retail-cost)/retail*100), '99') AS "MARKUP %" FROM BOOKS;
I've tried concatenating with both the + sign and the ampersand (&) sign, to no avail.
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.
I've installed XE and I want to know what accounts are active. If I log in to SQL*Plus as sys as SYSDBA how can I display a list of all user and admin accounts? I want to know who can log in.
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?
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
i have master-detail form.in master my bill_id gets generated when new form is open and i copy the same bill_id in detail(tabular)for each item.all the items which i enter in detail form get save the same bill_id which got generated.
in another form(which is tabular) i want to display bill_id's from detail form.but in detail form . There are same bill_id's more than once.but i want to display those bill_id's which are more than once only once.
I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values
For example:
So when dept.deptno=10, I want to display these 2 columns values 1. dept.deptno 2. dept.dname
otherwise, display these 2 columns values 1. dept.loc 2. null
Can this be done with one case, decode or "other" type of structure going thru the table one time??
SELECT emp.empno, emp.ename, CASE WHEN dept.deptno = 10 THEN to_char(dept.deptno) [code].......
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
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?
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.
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.
