Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Application Express sql HELP NEEDED!
Application Express sql HELP NEEDED! [message #484929] Fri, 03 December 2010 17:01 Go to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
First off, very nice site and a lot of info! I hate for my first post to be this, but I need some major help with Oracle Application Express. I took this database elective this semester and dropped it week 2 or at least thought I did. I'm in my fourth semester and my concentration is communications. Come to find out my drop didn't go through or someone dropped the ball. I should have web dropped, but I was in the office building ,so I thought since I was there I would do it. Anyways, the instructor knows me and knows that I'm serious about my classes and wouldn't just flake out and never come to class. He is giving me some extra time on the work and tests, but for the homework, it has to be done by Monday. I'm just going to bite the bullet here and do as much as I can and the instructor is going to let me do all tests open book. He is doing that to help me out. Now, most of the homework is with application express. ie: CONCAT three individual words etc ect. For the life of me I cannot get done what seems to be easy. I need help and I'm willing to pay a tutor fee to someone. If offering money is against the rules of this board, please accept my apologies. Thanks in advance.

Re: Application Express sql HELP NEEDED! [message #484970 is a reply to message #484929] Sat, 04 December 2010 09:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
On my screen, your message took almost 7 lines. Unfortunately, only (approx.) 1/4 of a single line is actual problem: "concat three individual words". What does that mean? What "three individual words"? Where are those words? In a database? On the screen? Text fields? Something different? Solution might be Oracle concatenation operator (double pipe - ||), or a JavaScript.

What I meant to say: perhaps it would have been better if it was other way round - 6 lines of problem description, 1 line of a student crying over his miserable life /forum/fa/1637/0/
Re: Application Express sql HELP NEEDED! [message #484972 is a reply to message #484970] Sat, 04 December 2010 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
/forum/fa/449/0/
Re: Application Express sql HELP NEEDED! [message #484976 is a reply to message #484972] Sat, 04 December 2010 11:44 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
"6 lines of problem description, 1 line of a student crying over his miserable life "

Hey *******, my sister died a few months ago from triple negative breast cancer, so I've been helping take care of her kids since we have no other family here. Here's a little more "crying" for you. I also work a full-time job and I'm a full-time student. I'm not asking for anyone to do this for me, but only to help me!
Rather than complain to the board of regents about my school messing up my drop, I thought I would just bust tail and get the job done. Instead of teasing me, wouldn't it have been more mature to not have posted your comment? So, since you two are "moderators" you might want to evaluate how you treat new members or go ahead and "ban" me because I told you off!



[mod-edit: profanity removed]

[Updated on: Sat, 04 December 2010 12:08] by Moderator

Report message to a moderator

Re: Application Express sql HELP NEEDED! [message #484977 is a reply to message #484976] Sat, 04 December 2010 12:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please try to do as much as you can yourself, then post what you get stuck on, after first checking the online documentation. You can find a menu to search the online documentation here:

http://www.oracle.com/pls/db112/homepage

For example, searching for concat and concatenation finds the following two links:

concat:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions033.htm#SQLRF00619

concatenation operator:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/operators003.htm#SQLRF51158

Most of the online documentation contains both syntax and examples.

When you post, please copy and paste what you ran, preferably from SQL*Plus, and the results, like so:

SCOTT@orcl_11gR2> select concat ('a', 'b') from dual;

CO
--
ab

1 row selected.

SCOTT@orcl_11gR2> select 'a' || 'b' from dual;

'A
--
ab

1 row selected.

SCOTT@orcl_11gR2>

Also, please provide your Oracle version, as some things are very different from one version to another. You can find your version like so:

SCOTT@orcl_11gR2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl_11gR2>


Re: Application Express sql HELP NEEDED! [message #484978 is a reply to message #484977] Sat, 04 December 2010 12:22 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Thank you Barbara. Realizing what I've got myself into and the workload has been making me a little anxious lately.Version 11g. I want to do it all myself, I'm not looking for someone to do "my" work. It's really Application Express that I'm having a problem with and the table/design part. I think I can feel my way through the syntax and such. So, for every problem, I have to create a table? Even for such things as CONCAT "Internet" and "Oracle"? I would think if you want to join to words as those then why would you have to create a table or can I use DUAL?

[Updated on: Sat, 04 December 2010 12:31]

Report message to a moderator

Re: Application Express sql HELP NEEDED! [message #484980 is a reply to message #484978] Sat, 04 December 2010 12:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
techwolf wrote on Sat, 04 December 2010 10:22

...So, for every problem, I have to create a table? Even for such things as CONCAT "Internet" and "Oracle"? I would think if you want to join to words as those then why would you have to create a table??


No, you do not have to create a table. That is one of the purposes of the dual table that exists on your system. If you describe dual and select from dual as shown below, you will see that it contains one row with one column and one value. The dual table should never be dropped or altered, just used for selecting from. You will find it has many uses, such as row generation that can be used to make a lot of things easier.

SCOTT@orcl_11gR2> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SCOTT@orcl_11gR2> select * from dual;

D
-
X

1 row selected.

SCOTT@orcl_11gR2>


Alternatively, you can set the value of a pl/sql variable without using select or dual, as shown below:

SCOTT@orcl_11gR2> set serveroutput on
SCOTT@orcl_11gR2> declare
  2    test_variable  varchar2 (30);
  3  begin
  4    test_variable := 'a' || 'b';
  5    dbms_output.put_line ('result:  ' || test_variable);
  6  end;
  7  /
result:  ab

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> 



Re: Application Express sql HELP NEEDED! [message #484981 is a reply to message #484977] Sat, 04 December 2010 12:31 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@techwolf, I'm really sorry for your loss, that's just too sad.

However, I still think that you should have explained the problem far better than you did (actually, you didn't explain it at all). If you expect any kind of help (except some blind shooting), put some more effort in it next time.
Re: Application Express sql HELP NEEDED! [message #484982 is a reply to message #484980] Sat, 04 December 2010 12:34 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Yea, I just edited my message re: DUAL.I've also been reading an 11g book and just read that. Thx
Re: Application Express sql HELP NEEDED! [message #484983 is a reply to message #484981] Sat, 04 December 2010 12:39 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
I agree. I've been just been a little on the panic side this week since I've realized I've bitten off more than I can handle. I haven't received a bad grade/mark yet so......I apologise.

I've already had an email from someone saying they will do it all for a fee and I declined. I want to do all my work, just needed help.

"I'm really sorry for your loss" Thank you, she was a great girl. A nurse for kids with cancer at St. Jude with a 3 and 1 year old.

[Updated on: Sat, 04 December 2010 12:45]

Report message to a moderator

Re: Application Express sql HELP NEEDED! [message #484984 is a reply to message #484983] Sat, 04 December 2010 12:48 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so what does that mean? Did Barbara's examples help enough so that you can handle the problem? (More precisely: handle it in Apex?)
Re: Application Express sql HELP NEEDED! [message #484985 is a reply to message #484983] Sat, 04 December 2010 12:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I've been in similar situations. Occasionally, you just have to muddle through the best you can. Sometimes you can retake a class and substitute the new grade, especially if there are extenuating circumstances that you can explain. It sounds like your instructor is doing everything possible to help.

I recall being very sick and dragging myself to class anyhow, after taking some "Coricidin D" decongestant. I used to work during the day and take once a week 4-hour lecture classes in the evening. That way I at least had till the next week to do homework for that class, so I could do it on my days off. We had a break at the midpoint. It was in a large auditorium style room and the "Coricidin D" made me sleepy and apparently I fell asleep. I woke up at break time and a lot of people were asking me if I had a nice nap. I don't know if the instructor called my name to answer a question or if I snored or what. I figured I wasn't learning anything and possibly disrupting the class, so there was no point in my being there, so I went home and went to bed.

When my father died, my car was stolen three days later, with all of my school books in the trunk of the car. I took two weeks off work to take care of things like arranging for cremation, cleaning out my Dad's apartment, renting a car, buying new school books, and so forth. By the time I got back to class, I had missed two weeks of class, was behind on reading the textbook, and without my notes.

In both cases, I muddled through with C's in Small Business Management and Sociology. Fortunately, they were just general education requirements, and didn't affect my computer skills. Both were decades ago.

Re: Application Express sql HELP NEEDED! [message #484987 is a reply to message #484977] Sat, 04 December 2010 13:08 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Version
Re: Application Express sql HELP NEEDED! [message #484988 is a reply to message #484987] Sat, 04 December 2010 13:25 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member


Problem 1.What's the position of "I" in "Oracle Internet Academy"? solved result:(select instr('Oracle Internet Academy','I') from dual)

Problem 2.Starting with the string"Oracle Internet Academy"pad the string to produce: Oracle$$$Internet$$$Academy
(2)Solved result:select replace('oracle internet academy',' ','$$$$') from dual

Problem:3. Using three separate words,"Oracle,
Internet, and Academy",use one command to produce the following
output:"The best class Oracle Internet Academy"
my solution: select 'The Best Class' || ' ' || Oracle || ' ' || Internet || ' ' || Academy from DUAL; (ORA-00904: "ACADEMY": invalid identifier)
Re: Application Express sql HELP NEEDED! [message #484989 is a reply to message #484988] Sat, 04 December 2010 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle, Internet and Academy must be enclose between ' to be a static string otherwise they are columns, in the same way you wrote 'The Best Class' .

Also but your statements and code between [code] and [/code] tags, then it is more readable (above all when there are several lines and/or '' or "):

select 'The Best Class' || ' ' || 'Oracle' || ' ' || 'Internet' || ' ' || 'Academy' from DUAL; 


Regards
Michel

[Updated on: Sat, 04 December 2010 13:39]

Report message to a moderator

Re: Application Express sql HELP NEEDED! [message #484990 is a reply to message #484989] Sat, 04 December 2010 13:55 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
I knew I was forgetting (' ')something. Thank you.
Re: Application Express sql HELP NEEDED! [message #484991 is a reply to message #484990] Sat, 04 December 2010 14:29 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Do I do most DUAL queries from: Home>SQL Workshop>SQL Commands ??

I am in the correct place? Right? I know all this stuff is probably simple for you guys and my questions are irritating, but I do appreciate your help!
Re: Application Express sql HELP NEEDED! [message #484992 is a reply to message #484991] Sat, 04 December 2010 15:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
techwolf wrote on Sat, 04 December 2010 12:29

Do I do most DUAL queries from: Home>SQL Workshop>SQL Commands?? I am in the correct place? Right?


In apex, yes. Do you also have access to SQL*Plus or just apex? Do you have an Oracle database installed on a home computer or are you connecting to your school computer through the internet?
Re: Application Express sql HELP NEEDED! [message #484993 is a reply to message #484992] Sat, 04 December 2010 15:47 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Connecting to iacademy.oracle.com for the queries etc. (Oracle Academy)
There are 9 sections with 3-4 items in each. Every section also has 4-5 quizzes that I'm currently doing right now. I've gotten 3 sections done after you told me of DUAL and I'm chopping through all of it O.K just the whole database/table design part is where I think I'm going to have the most trouble. Now, that I know about DUAL, coming up with the query syntax isn't that bad. I've got finals next week so, that's weighing on me as well, but I know other people have done it and more, so I can get through it as well. Just gotta keep truckin.

[Updated on: Sat, 04 December 2010 15:50]

Report message to a moderator

Re: Application Express sql HELP NEEDED! [message #484995 is a reply to message #484993] Sat, 04 December 2010 17:17 Go to previous messageGo to next message
techwolf
Messages: 11
Registered: December 2010
Junior Member
Problem: Display Oracle database employee last_name and salary for employee_ids between 100 and 102. Include a third column that divides each salary by 1.55 and rounds the result to two decimal places.

So, I need to create the table correct? You cannot use DUAL? After I create the table, my query should be:

Select last_name, salary, round(salary/1.55,2) as 'third_column' from my_table_name;


Re: Application Express sql HELP NEEDED! [message #484997 is a reply to message #484995] Sat, 04 December 2010 17:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

So, I need to create the table correct?


I don't know what you may already have. Try

describe employees

to see if there is an employees table. That is one of the standard demo tables that Oracle uses, so it may already be there. If not, then you will need to create it and enter the data.

Quote:

You cannot use DUAL?


No.

Quote:

After I create the table, my query should be:

Select last_name, salary, round(salary/1.55,2) as 'third_column' from my_table_name;


You need to either not put quote around third_column or put double quotes around it. The as is optional. Using double quotes preserves the case and allows spaces and things not otherwise allowed. Without the quotes, everything is upper case. As a general rule, it is better to let everything default to upper case, so that it is easy to reference it. Anything that is created using quotes, must later be referenced using quotes.

Example using a different table:

SCOTT@orcl_11gR2> describe emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SCOTT@orcl_11gR2> select ename, sal, round(sal/1.55,2) as third_column
  2  from   emp
  3  /

ENAME             SAL THIRD_COLUMN
---------- ---------- ------------
SMITH             800       516.13
ALLEN            1600      1032.26
WARD             1250       806.45
JONES            2975      1919.35
MARTIN           1250       806.45
BLAKE            2850      1838.71
CLARK            2450      1580.65
SCOTT            3000      1935.48
KING             5000      3225.81
TURNER           1500       967.74
ADAMS            1100       709.68
JAMES             950        612.9
FORD             3000      1935.48
MILLER           1300       838.71

14 rows selected.

SCOTT@orcl_11gR2> select ename, sal, round(sal/1.55,2) as "third_column"
  2  from   emp
  3  /

ENAME             SAL third_column
---------- ---------- ------------
SMITH             800       516.13
ALLEN            1600      1032.26
WARD             1250       806.45
JONES            2975      1919.35
MARTIN           1250       806.45
BLAKE            2850      1838.71
CLARK            2450      1580.65
SCOTT            3000      1935.48
KING             5000      3225.81
TURNER           1500       967.74
ADAMS            1100       709.68
JAMES             950        612.9
FORD             3000      1935.48
MILLER           1300       838.71

14 rows selected.

SCOTT@orcl_11gR2>




Re: Application Express sql HELP NEEDED! [message #484998 is a reply to message #484997] Sat, 04 December 2010 17:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can also:

select table_name from user_tables;

to get a list of tables that you already have.
Re: Application Express sql HELP NEEDED! [message #485028 is a reply to message #484995] Sun, 05 December 2010 06:27 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
techwolf wrote on Sun, 05 December 2010 00:17
So, I need to create the table correct? You cannot use DUAL?

[Pretend you never saw this message, at least for the time being]

Saying that you "can not" use DUAL is not entirely true. You can create your own "virtual" table using dual, using WITH subquery factoring clause. Here's an example:
SQL> with my_employees as
  2    (select 'Little' last_name, 100 salary from dual
  3     union
  4     select 'Foot', 200 from dual
  5     union
  6     select 'Tech', 300 from dual
  7     union
  8     select 'Wolf', 400 from dual
  9    )
 10    select last_name,
 11           salary,
 12           round (salary / 1.55, 2) third_column
 13    from my_employees;

LAST_N     SALARY THIRD_COLUMN
------ ---------- ------------
Foot          200       129.03
Little        100        64.52
Tech          300       193.55
Wolf          400       258.06

SQL>


P.S. I think that "employees" table exists in a schema you are connected to, so - most probably you'll find its exact name using Barbara's query (the one she provided in a message above this one). So - most probably you don't have to create one.

[Updated on: Sun, 05 December 2010 06:29]

Report message to a moderator

Previous Topic: How to set form items at page load
Next Topic: Master Details Form
Goto Forum:
  


Current Time: Thu Mar 28 13:26:52 CDT 2024