[cswtsjaij]

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

[Amadeus]

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

[cswtsjaij]

Amadeus, 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

[born2c0de]

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.

[cswtsjaij]

born2c0de, 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.

[Jayman]

cswtsjaij, 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.

[cswtsjaij]

jayman9, on 19 Jul, 2006 - 07:38 PM, said:

cswtsjaij, 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.

[cswtsjaij]

jayman9, on 19 Jul, 2006 - 07:38 PM, said:

cswtsjaij, 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?

[Jayman]

cswtsjaij, 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.