8 Replies - 10441 Views - Last Post: 21 July 2006 - 01:11 PM Rate Topic: -----

#1 cswtsjaij  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 30-June 06

SQL Plus Question

Posted 18 July 2006 - 04:07 PM

I have a SQL Plus problem I am working on and I am confused. Here is what I need to do:

Format columns as follows: STUDENT_ID column with FORMAT 99; PHONE with FORMAT xxx-xxx-xxxx; REGISTRATION_DATE with FORMAT MM-DD-YYYY. Show all SQL*PLUS statements used and all outputs.

Can someone tell me what I need to do?

THANKS
Is This A Good Question/Topic? 0
  • +

Replies To: SQL Plus Question

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: SQL Plus Question

Posted 18 July 2006 - 06:47 PM

Certainly..it's just a matter of using the COLUMN and FORMAT commands...you can take a look here for examples:
http://www.psoug.org...ce/sqlplus.html
http://www.ss64.com/...x/plus_fmt.html
Was This Post Helpful? 0
  • +
  • -

#3 cswtsjaij  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 30-June 06

Re: SQL Plus Question

Posted 19 July 2006 - 07:18 AM

View PostAmadeus, on 18 Jul, 2006 - 05:39 PM, said:

Certainly..it's just a matter of using the COLUMN and FORMAT commands...you can take a look here for examples:
http://www.psoug.org...ce/sqlplus.html
http://www.ss64.com/...x/plus_fmt.html



So would I enter the column format as:

COLUMN REGISTRATION_DATE FORMAT MM-DD-YYYY

If this is not correct how would I enter this command?

THANKS
Was This Post Helpful? 0
  • +
  • -

#4 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: SQL Plus Question

Posted 19 July 2006 - 08:08 AM

Do you want to create a table?
Use this CREATE TABLE <table name>(column_name <type>, ...);

And you can't use the LONG type for the phone...you have to treat it as a string.
Was This Post Helpful? 0
  • +
  • -

#5 cswtsjaij  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 30-June 06

Re: SQL Plus Question

Posted 19 July 2006 - 07:12 PM

View Postborn2c0de, on 19 Jul, 2006 - 07:00 AM, said:

Do you want to create a table?
Use this CREATE TABLE <table name>(column_name <type>, ...);

And you can't use the LONG type for the phone...you have to treat it as a string.



I am trying to perform the FORMAT command on an existing table.
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: SQL Plus Question

Posted 19 July 2006 - 08:46 PM

View Postcswtsjaij, on 19 Jul, 2006 - 06:10 AM, said:

So would I enter the column format as:

COLUMN REGISTRATION_DATE FORMAT MM-DD-YYYY

If this is not correct how would I enter this command?

THANKS


To format the date in that type of format requires two steps:

First for the column itself you are just going to specify the column width.
COLUMN REGISTRATION_DATE FORMAT A10



Then to get the date in the correct format, use the TO_CHAR(someColumn, 'MM-DD-YYYY') inside your SQL statement.

For example:
SELECT TO_CHAR(someColumn, 'MM-DD-YYYY') "Date" FROM yourTable



This will return a date in the format you need. Just replace someColumn with the name of the correct column and "Date" is an alias, so you can make it whatever you need it say.
Was This Post Helpful? 0
  • +
  • -

#7 cswtsjaij  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 30-June 06

Re: SQL Plus Question

Posted 20 July 2006 - 09:11 AM

View Postjayman9, on 19 Jul, 2006 - 07:38 PM, said:

View Postcswtsjaij, on 19 Jul, 2006 - 06:10 AM, said:

So would I enter the column format as:

COLUMN REGISTRATION_DATE FORMAT MM-DD-YYYY

If this is not correct how would I enter this command?

THANKS


To format the date in that type of format requires two steps:

First for the column itself you are just going to specify the column width.
COLUMN REGISTRATION_DATE FORMAT A10



Then to get the date in the correct format, use the TO_CHAR(someColumn, 'MM-DD-YYYY') inside your SQL statement.

For example:
SELECT TO_CHAR(someColumn, 'MM-DD-YYYY') "Date" FROM yourTable



This will return a date in the format you need. Just replace someColumn with the name of the correct column and "Date" is an alias, so you can make it whatever you need it say.


Thank you for your help I greatly appreciate it. :D
Was This Post Helpful? 0
  • +
  • -

#8 cswtsjaij  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 30-June 06

Re: SQL Plus Question

Posted 21 July 2006 - 08:18 AM

View Postjayman9, on 19 Jul, 2006 - 07:38 PM, said:

View Postcswtsjaij, on 19 Jul, 2006 - 06:10 AM, said:

So would I enter the column format as:

COLUMN REGISTRATION_DATE FORMAT MM-DD-YYYY

If this is not correct how would I enter this command?

THANKS


To format the date in that type of format requires two steps:

First for the column itself you are just going to specify the column width.
COLUMN REGISTRATION_DATE FORMAT A10



Then to get the date in the correct format, use the TO_CHAR(someColumn, 'MM-DD-YYYY') inside your SQL statement.

For example:
SELECT TO_CHAR(someColumn, 'MM-DD-YYYY') "Date" FROM yourTable



This will return a date in the format you need. Just replace someColumn with the name of the correct column and "Date" is an alias, so you can make it whatever you need it say.




Display the results as follows: (SHOW THE OUTPUT). Include a header, with the title: Registration Date Report. Display the columns in the following order: STUDENT_ID,PHONE, then REGISTRATION_DATE column in the result set.

I am not sure what I am to do with this problem. Is it asking to add a header and display just the column titles? Or is it asking to add a header and display the columns and all data that falls under these columns? Is there a way to display just the column titles?
Was This Post Helpful? 0
  • +
  • -

#9 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: SQL Plus Question

Posted 21 July 2006 - 01:11 PM

View Postcswtsjaij, on 21 Jul, 2006 - 07:10 AM, said:

Display the results as follows: (SHOW THE OUTPUT). Include a header, with the title: Registration Date Report. Display the columns in the following order: STUDENT_ID,PHONE, then REGISTRATION_DATE column in the result set.

I am not sure what I am to do with this problem. Is it asking to add a header and display just the column titles? Or is it asking to add a header and display the columns and all data that falls under these columns? Is there a way to display just the column titles?


It is saying that you will create a header title using TTITLE. And your display of the returned results will be displayed with the columns in that specific order.

For example:
SELECT STUDENT_ID, PHONE, REGISTRATION_DATE FROM STUDENTTABLE


Would return:

STUDENT_ID	 PHONE	 REGISTRATION_DATE
----------	 -----	 -----------------

The order that your column names are in inside the SELECT statement determines the order of the columns in the returned results.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1