• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

SQL Ouery in Oracle

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
pinkuray
Asked:
pinkuray
  • 13
  • 4
  • 2
  • +1
4 Solutions
 
Aaron ShiloCommented:
0
 
pinkurayAuthor Commented:
I tried using UNPIVOT this query but not working for me

0
 
pinkurayAuthor Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
SharathData EngineerCommented:
What is your Oracle version?
0
 
pinkurayAuthor Commented:
My database version is :

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
0
 
pinkurayAuthor Commented:
cant we use the PIVOT and UNPIVOT Operators in Oracle Database 10G  ?
0
 
pinkurayAuthor Commented:
@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
 
SharathData EngineerCommented:
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
 
pinkurayAuthor Commented:
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
 
pinkurayAuthor Commented:
can you send me the code for my tables to get the expected result?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
@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
 
pinkurayAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pinkurayAuthor Commented:
I have posted another question related to this can you help me in that ...
0
 
pinkurayAuthor Commented:
questions is on " Pivoting in SQL using the 10g"
0
 
pinkurayAuthor Commented:
it is similar to this question only difference is shift are fixed for the whole month.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the question link to that one ?
0
 
pinkurayAuthor Commented:
0
 
pinkurayAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now