Solved

SQL Ouery in Oracle

Posted on 2011-03-02
20
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 4
  • 2
  • +1
20 Comments
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 100 total points
ID: 35024931
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35024962
I tried using UNPIVOT this query but not working for me

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35024988
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35024997
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 41

Expert Comment

by:Sharath
ID: 35025019
What is your Oracle version?
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35025031
My database version is :

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

Author Comment

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

Accepted Solution

by:
pinkuray earned 0 total points
ID: 35025398
@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 41

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 35025415
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
ID: 35025416
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
 
LVL 4

Author Comment

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

Expert Comment

by:Naveen Kumar
ID: 35025547
@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
ID: 35026386
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
ID: 35034106
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
ID: 35034141
I have posted another question related to this can you help me in that ...
0
 
LVL 4

Author Comment

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

Author Comment

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

Expert Comment

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

Author Comment

by:pinkuray
ID: 35034208
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35067802
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

696 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