 | |
how to generate the following output in sql ?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| ||||
| ||||
|
how to generate the following output in sql ? -
11-10-2008
, 12:22 PM
|
Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ pub_code | varchar(3) | YES | | NULL | | status_flag | varchar(2) | YES | | NULL | | user_id | varchar(15) | YES | | NULL | | entry_date | date | YES | | NULL | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ |
select * from mis_outtrack;
+----------+-------------+---------+------------+------------+
Quote:
|
pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ SND | RE | AMY | 2008-11-01 | 2008-11-01 | SND | AU | AMY | 2008-11-05 | 2008-11-01 | SND | SE | AMY | 2008-11-10 | 2008-11-01 | MND | RE | AMY | 2008-11-03 | 2008-11-02 | MND | AU | AMY | 2008-11-15 | 2008-11-02 | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ |
here pub_code is short name for a particular news paper
useid is the user who is updating the records
pub_date is the date of publication of that news paper
and
entry_date is the date when the user is updating the inserting
the status of that publication
here RE means recieved
AU means audited
SE means send
there can only be these three status flags
I have to write an sql to display this data in the following format
------------------------------------------------------------------------
pub_code |entry_date |entry_date |entry_date |pub_date
Quote:
|
(for a |(when |(when |(when |(publication particular|status was RE)|status was AU)|status was SE |date for pub_code) | | | |that news |
------------------------------------------------------------------------
Quote:
|
SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 |
how can I do this in sql
any help will be appriciated.
Thanks in advance
#2
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code | varchar(3) | YES | | NULL | | | status_flag | varchar(2) | YES | | NULL | | | user_id | varchar(15) | YES | | NULL | | | entry_date | date | YES | | NULL | | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code |entry_date |entry_date |entry_date |pub_date | (for a |(when |(when |(when |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| ------------------------------------------------------------------------ | SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 | MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance |
See http://www.psoug.org/reference/decode_case.html
Regards
Michel
#3
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code | varchar(3) | YES | | NULL | | | status_flag | varchar(2) | YES | | NULL | | | user_id | varchar(15) | YES | | NULL | | | entry_date | date | YES | | NULL | | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code |entry_date |entry_date |entry_date |pub_date | (for a |(when |(when |(when |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| ------------------------------------------------------------------------ | SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 | MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance |
See http://www.psoug.org/reference/decode_case.html
Regards
Michel
#4
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code | varchar(3) | YES | | NULL | | | status_flag | varchar(2) | YES | | NULL | | | user_id | varchar(15) | YES | | NULL | | | entry_date | date | YES | | NULL | | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code |entry_date |entry_date |entry_date |pub_date | (for a |(when |(when |(when |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| ------------------------------------------------------------------------ | SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 | MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance |
See http://www.psoug.org/reference/decode_case.html
Regards
Michel
#5
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code | varchar(3) | YES | | NULL | | | status_flag | varchar(2) | YES | | NULL | | | user_id | varchar(15) | YES | | NULL | | | entry_date | date | YES | | NULL | | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code |entry_date |entry_date |entry_date |pub_date | (for a |(when |(when |(when |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| ------------------------------------------------------------------------ | SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 | MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance |
See http://www.psoug.org/reference/decode_case.html
Regards
Michel
#6
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field * * * | Type * * * *| Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code * *| varchar(3) *| YES *| * * | NULL * *| * * * | | status_flag | varchar(2) *| YES *| * * | NULL * *| * * * | | user_id * * | varchar(15) | YES *| * * | NULL * *| * * * | | entry_date *| date * * * *| YES *| * * | NULL * *| * * * | | pub_date * *| date * * * *| YES *| * * | NULL * *| * * * | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date * | +----------+-------------+---------+------------+------------+ | SND * * *| RE * * * * *| AMY * * | 2008-11-01 | 2008-11-01 | | SND * * *| AU * * * * *| AMY * * | 2008-11-05 | 2008-11-01 | | SND * * *| SE * * * * *| AMY * * | 2008-11-10 | 2008-11-01 | | MND * * *| RE * * * * *| AMY * * | 2008-11-03 | 2008-11-02 | | MND * * *| AU * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | | MND * * *| SE * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved * * *AU means audited * * *SE means send * *there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code *|entry_date * *|entry_date * *|entry_date * *|pub_date | (for a * *|(when * * * * |(when * * * * |(when * * * * |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | * * * * * * *| * * * * * * *| ** * * * * *|that news paper| ------------------------------------------------------------------------ | *SND * * * *2008-11-01 * *2008-11-05 * * 2008-11-10 * *2008-11-01 | *MND * * * *2008-11-03 * *2008-11-15 * * 2008-11-15 2008-11-02 | | how can I do this in sql *any help will be appriciated. Thanks in advance |
question in an Oracle newsgroup?
David Fitzjarrell
#7
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field * * * | Type * * * *| Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code * *| varchar(3) *| YES *| * * | NULL * *| * * * | | status_flag | varchar(2) *| YES *| * * | NULL * *| * * * | | user_id * * | varchar(15) | YES *| * * | NULL * *| * * * | | entry_date *| date * * * *| YES *| * * | NULL * *| * * * | | pub_date * *| date * * * *| YES *| * * | NULL * *| * * * | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date * | +----------+-------------+---------+------------+------------+ | SND * * *| RE * * * * *| AMY * * | 2008-11-01 | 2008-11-01 | | SND * * *| AU * * * * *| AMY * * | 2008-11-05 | 2008-11-01 | | SND * * *| SE * * * * *| AMY * * | 2008-11-10 | 2008-11-01 | | MND * * *| RE * * * * *| AMY * * | 2008-11-03 | 2008-11-02 | | MND * * *| AU * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | | MND * * *| SE * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved * * *AU means audited * * *SE means send * *there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code *|entry_date * *|entry_date * *|entry_date * *|pub_date | (for a * *|(when * * * * |(when * * * * |(when * * * * |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | * * * * * * *| * * * * * * *| ** * * * * *|that news paper| ------------------------------------------------------------------------ | *SND * * * *2008-11-01 * *2008-11-05 * * 2008-11-10 * *2008-11-01 | *MND * * * *2008-11-03 * *2008-11-15 * * 2008-11-15 2008-11-02 | | how can I do this in sql *any help will be appriciated. Thanks in advance |
question in an Oracle newsgroup?
David Fitzjarrell
#8
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field * * * | Type * * * *| Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code * *| varchar(3) *| YES *| * * | NULL * *| * * * | | status_flag | varchar(2) *| YES *| * * | NULL * *| * * * | | user_id * * | varchar(15) | YES *| * * | NULL * *| * * * | | entry_date *| date * * * *| YES *| * * | NULL * *| * * * | | pub_date * *| date * * * *| YES *| * * | NULL * *| * * * | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date * | +----------+-------------+---------+------------+------------+ | SND * * *| RE * * * * *| AMY * * | 2008-11-01 | 2008-11-01 | | SND * * *| AU * * * * *| AMY * * | 2008-11-05 | 2008-11-01 | | SND * * *| SE * * * * *| AMY * * | 2008-11-10 | 2008-11-01 | | MND * * *| RE * * * * *| AMY * * | 2008-11-03 | 2008-11-02 | | MND * * *| AU * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | | MND * * *| SE * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved * * *AU means audited * * *SE means send * *there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code *|entry_date * *|entry_date * *|entry_date * *|pub_date | (for a * *|(when * * * * |(when * * * * |(when * * * * |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | * * * * * * *| * * * * * * *| ** * * * * *|that news paper| ------------------------------------------------------------------------ | *SND * * * *2008-11-01 * *2008-11-05 * * 2008-11-10 * *2008-11-01 | *MND * * * *2008-11-03 * *2008-11-15 * * 2008-11-15 2008-11-02 | | how can I do this in sql *any help will be appriciated. Thanks in advance |
question in an Oracle newsgroup?
David Fitzjarrell
#9
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field * * * | Type * * * *| Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code * *| varchar(3) *| YES *| * * | NULL * *| * * * | | status_flag | varchar(2) *| YES *| * * | NULL * *| * * * | | user_id * * | varchar(15) | YES *| * * | NULL * *| * * * | | entry_date *| date * * * *| YES *| * * | NULL * *| * * * | | pub_date * *| date * * * *| YES *| * * | NULL * *| * * * | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date * | +----------+-------------+---------+------------+------------+ | SND * * *| RE * * * * *| AMY * * | 2008-11-01 | 2008-11-01 | | SND * * *| AU * * * * *| AMY * * | 2008-11-05 | 2008-11-01 | | SND * * *| SE * * * * *| AMY * * | 2008-11-10 | 2008-11-01 | | MND * * *| RE * * * * *| AMY * * | 2008-11-03 | 2008-11-02 | | MND * * *| AU * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | | MND * * *| SE * * * * *| AMY * * | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved * * *AU means audited * * *SE means send * *there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code *|entry_date * *|entry_date * *|entry_date * *|pub_date | (for a * *|(when * * * * |(when * * * * |(when * * * * |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | * * * * * * *| * * * * * * *| ** * * * * *|that news paper| ------------------------------------------------------------------------ | *SND * * * *2008-11-01 * *2008-11-05 * * 2008-11-10 * *2008-11-01 | *MND * * * *2008-11-03 * *2008-11-15 * * 2008-11-15 2008-11-02 | | how can I do this in sql *any help will be appriciated. Thanks in advance |
question in an Oracle newsgroup?
David Fitzjarrell
#10
| |||
| |||
|
|
I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | pub_code | varchar(3) | YES | | NULL | | | status_flag | varchar(2) | YES | | NULL | | | user_id | varchar(15) | YES | | NULL | | | entry_date | date | YES | | NULL | | | pub_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ select * from mis_outtrack; +----------+-------------+---------+------------+------------+ | pub_code | status_flag | user_id | entry_date | pub_date | +----------+-------------+---------+------------+------------+ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +----------+-------------+---------+------------+------------+ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format ------------------------------------------------------------------------ pub_code |entry_date |entry_date |entry_date |pub_date | (for a |(when |(when |(when |(publication | particular|status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| ------------------------------------------------------------------------ | SND 2008-11-01 2008-11-05 2008-11-10 2008-11-01 | MND 2008-11-03 2008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance |
usenet group and it is plain to everyone that VARCHAR is not an Oracle
data type. As a student it is for you to work out the answer not to
rummage around forums asking others to do your work for you.
Sorry for the harshness of my response but developers know which
product they are working with. And the answer in Oracle may be
different from the answer in SQL Server.
--
Daniel A. Morgan
Puget Sound Oracle Users Group
www.psoug.org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.