Solved

SQL Ouery in Oracle

Posted on 2011-03-02
20
446 Views
Last Modified: 2012-05-11
Hello,

I have a table with few data, I want to have a report from that table as like below:

WORK_DATE      DBA      WORK_STATUS      SHIFT_TYPE
Tuesday, March 01, 2011      JOHN      Yes      Morning
Tuesday, March 01, 2011      PINKURAY      Weekoff      Null
Tuesday, March 01, 2011      MARK      Yes      Morning
Wednesday, March 02, 2011      JOHN      Yes      Morning
Wednesday, March 02, 2011      PINKURAY      Yes      Afternoon
Wednesday, March 02, 2011      MARK      Yes      Afternoon
Thursday, March 03, 2011      JOHN      Yes      Morning
Thursday, March 03, 2011      PINKURAY      Yes      Afternoon
Thursday, March 03, 2011      MARK      Yes      Afternoon
Friday, March 04, 2011      JOHN      Yes      Morning
Friday, March 04, 2011      PINKURAY      Yes      Afternoon
Friday, March 04, 2011      MARK      Yes      Afternoon
Saturday, March 05, 2011      JOHN      Weekoff      Null
Saturday, March 05, 2011      PINKURAY      Yes      Afternoon
Saturday, March 05, 2011      MARK      Weekoff      Null
Sunday, March 06, 2011      JOHN      Weekoff      Null
Sunday, March 06, 2011      PINKURAY      Yes      Afternoon
Sunday, March 06, 2011      MARK      Weekoff      Null
Monday, March 07, 2011      JOHN      Yes      Morning
Monday, March 07, 2011      PINKURAY      Weekoff      Null
Monday, March 07, 2011      MARK      Yes      Morning


The Source table having the data is been attached here and the structure of the table is like below:

 
/* 
W_DAY : "W" STANDS FOR THE WORKING , WHERE EVER THE W_ COLUMN WILL BE FILLED AS YES OR WEEKOFF
S_DAY : "S" STANDS FOR SHIFT, IT CAN BE Morning OR Afternoon OR Night OR General
*/

CREATE TABLE  "SHIFT_SUMMARY" 
   ( "SRNO" NUMBER, 
 "DBA" VARCHAR2(10), 
 "W_MON" VARCHAR2(50), 
 "S_MON" VARCHAR2(50), 
 "W_TUE" VARCHAR2(50), 
 "S_TUE" VARCHAR2(50), 
 "W_WED" VARCHAR2(50), 
 "S_WED" VARCHAR2(50), 
 "W_THU" VARCHAR2(50), 
 "S_THU" VARCHAR2(50), 
 "W_FRI" VARCHAR2(50), 
 "S_FRI" VARCHAR2(50), 
 "W_SAT" VARCHAR2(50), 
 "S_SAT" VARCHAR2(50), 
 "W_SUN" VARCHAR2(50), 
 "S_SUN" VARCHAR2(50), 
 "MONTH" VARCHAR2(50), 
 "YEAR" NUMBER, 
  CONSTRAINT "SHIFT_SUMMARY_PK" PRIMARY KEY ("SRNO") ENABLE
   )
/

CREATE SEQUENCE SHIFT_SUMMARY_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

CREATE OR REPLACE TRIGGER  "SHIFT_SUMMARY_BR_I" 
  before insert on "SHIFT_SUMMARY"               
  for each row  
begin   
  if :NEW."SRNO" is null then 
    select "SHIFT_SUMMARY_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "SHIFT_SUMMARY_BR_I" ENABLE
/

Open in new window


The expected result is  also attached here.

SAMPLE-DATA.xls
0
Comment
Question by:pinkuray
  • 13
  • 4
  • 2
  • +1
20 Comments
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 100 total points
Comment Utility
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
I tried using UNPIVOT this query but not working for me

0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
I used the query like below for getting working days as :

 
SELECT *
FROM   SHIFT_SUMMARY
UNPIVOT INCLUDE NULLS 
(WORKINGDAYS FOR WORKINGWEEKS 
IN (W_MON AS 'WORKING_ON_MONDAY', W_TUE AS 'WORKING_ON_TUE', W_WED AS 'WORKING_ON_WED', W_THU AS 'WORKING_ON_THU' ,W_FRI AS 'WORKING_ON_FRI',W_SAT AS 'WORKING_ON_SAT',W_SUN AS 'WORKING_ON_SUN'));

Open in new window


Error:
ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Even if run the queries that are given on the link is also not working.

Can you please guide me what is wrong in this?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
the expected output for "Work_Date" column shows march 1st to 6th onwards, but this date itself is not there in your table data. So how do you get this ?

Also i believe PIVOT is best option to use here if you are on oracle 11g.

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
What is your Oracle version?
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
My database version is :

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
cant we use the PIVOT and UNPIVOT Operators in Oracle Database 10G  ?
0
 
LVL 4

Accepted Solution

by:
pinkuray earned 0 total points
Comment Utility
@nav_kum_v:

If we do a calendar in oracle then it will show the similar way what my table structure is like as below taken for example of this month:

SUN      MON      TUE      WED      THU      FRI      SAT
            1      2      3      4      5
6      7      8      9      10      11      12
13      14      15      16      17      18      19
20      21      22      23      24      25      26
27      28      29      30      31            

now if I create a table called calendar then can we join this to my table and get the dates ?


 
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
Comment Utility
PIVOT and UNPIVOT are implemented in 11g. You can try PIVOT with MODEL clause. Check these examples.
http://technology.amis.nl/blog/300/pivoting-in-sql-using-the-10g-model-clause
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
based on this article we can use the same functionality in 10g too:

http://www.bobjankovsky.org/show.php?seq=97

When I tried I got the same error as I mentioned before.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:pinkuray
Comment Utility
can you send me the code for my tables to get the expected result?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
@pinkuray,

You should have told about your calendar table/join idea before itself.

just give the table script / sample data for your calendar table for me to setup that in my test database.

But i believe it is not an easy query to do without using the PIVOT. but 10g does not have PIVOT/UNPIVOT etc.

0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
No I just thought of having a calendar table which may help me to do this. As I don't have any table for calendar but when you try to use a calender you will see similar way I have designed the table structure.

So thought of having a table or just going thought it will help.

I want a solution for my issue, even having PIVOT or not.

Can you please help me in this.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 150 total points
Comment Utility
I am not sure whether the calendar table can help here or not, but you can set it up in your test database. Then send me the script / insert scripts for the data into this calendar table and then i can setup the same in my test database to see whether some join query can get the information you are looking for.
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
I have posted another question related to this can you help me in that ...
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
questions is on " Pivoting in SQL using the 10g"
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
it is similar to this question only difference is shift are fixed for the whole month.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
what is the question link to that one ?
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
0
 
LVL 4

Author Closing Comment

by:pinkuray
Comment Utility
Accepting this point s as my logic was not up to the mark but all your post where very helpful to build my new logic thanks a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now