9 Replies Latest reply: May 28, 2013 5:58 AM by dba-india RSS

    Help in a query please

    dba-india
      Please help as to what code would be required to prepare xls files for each department manager to receive email related to his employee records.
      Sample data is as given below,


      EMPNO     ENAME     SALARY     DEPTMANAGER     EMAIL
      --------------------------------------------------
      E1     NAME1     1234     7          ASDX@GMAIL.COM
      E2     NAME2     2340     7          QERA@GMAIL.COM
      E3     NAME3     9334     3          QWER@YAHOO.COM
      E4     NAME4     2345     3          ASFD@INDIA.COM
      E5     NAME5     1463     1          DWZS@GMAIL.COM
      E6     NAME6     3444     1          ZGBA@YAHOO.COM

      i.e. for example as per the above data,
      department manager 7 should recieve his employee records with all details
      department manager 3 should recieve his employee records with all details
      department manager 1 should recieve his employee records with all details

      Thanks in advance.
        • 1. Re: Help in a query please
          Hoek
          No database version, no CREATES, no INSERTS....you missed {message:id=9360002} ?
          • 2. Re: Help in a query please
            sb92075
            How do I ask a question on the forums?
            SQL and PL/SQL FAQ

            Handle:     DatabaseAdministrator
            Status Level:     Pro (570)
            Registered:     Jul 2, 2007
            Total Posts:     1,488
            Total Questions:     155 (124 unresolved)


            I extend to you my condolences; since you rarely get your questions answered here.
            • 3. Re: Help in a query please
              dba-india
              I can understand how Hoek & sb92075 are making +1 to the number of posts by just commenting rather than giving proper answers. :-) So this is a good way of getting additional +1 to the number of posts you made. That is why some people never reach ace list yet have so many points, so sad.

              My db version is 11g, any expert who can answer to my question. Thanks in advance.
              • 4. Re: Help in a query please
                EdStevens
                DatabaseAdministrator wrote:
                I can understand how Hoek & sb92075 are making each point by just commenting rather than giving proper answers. :-) So this is a good way of getting additional +1 to the number of replies you made. That is why some people never reach ace list yet have so many points, so sad.
                I can assure you that neither Hoek nor sb92075 give a flying fig about ponts.
                I can also assure you that points are NOT accumulated simply by posting. The only way points are awarded is when an OP - and only an OP - marks a response as 'helpful' or 'correct'. Something the record shows that you seldom do.
                I can further assure you that there is zero relationship between forum points and Oracle's awarding an ACE desgination. The ACE program is totallly seperate from this forum.
                >
                My db version is 11g, any expert who can answer to my question. Thanks in advance.
                I have read your originial question serveral times and I'm afraid the only 'expert' that can answer it to your satisfaction would be an expert mind reader. I would expect that someone with over 1400 posts over nearly six years would understand how to ask a question, and would understand the forum ettiquette.
                • 5. Re: Help in a query please
                  dba-india
                  +1 to number of posts of EdStevens, and thanks to clarify that its a difficult question for you too :-)
                  • 6. Re: Help in a query please
                    dba-india
                    And moreover the ettiquette is if any one of you don't know the answer to the question then don't dig your nose into the thread and start spoiling it.
                    • 7. Re: Help in a query please
                      Karthick_Arp
                      DatabaseAdministrator wrote:
                      Please help as to what code would be required to prepare xls files for each department manager to receive email related to his employee records.
                      XLS is microsoft proprietary format. So preparing a XLS file from oracle is going to be quite a job to do. So first can you settle down with a CSV file which can be viewed from Microsoft Excel?

                      If the answer is YES search for DUMP_CSV in asktom.oracle.com. This will give you an idea of how to write the output of a SQL SELECT statement into a file

                      If the answer is NO then read the FAQ {message:id=9360007} This will give you some idea on how to write the result of a SELECT statement into a XLS format file.

                      Sample data is as given below,
                      When ever posting sample data remember to use {noformat}
                      {noformat} tag to preserve the format. If you don't use them the post will look like a cr@p. To know how a cr@py post looks, look into your original post. See below how nice its when you use it. 
                      EMPNO ENAME SALARY DEPTMANAGER EMAIL
                      --------------------------------------------------
                      E1 NAME1 1234 7 ASDX@GMAIL.COM
                      E2 NAME2 2340 7 QERA@GMAIL.COM
                      E3 NAME3 9334 3 QWER@YAHOO.COM
                      E4 NAME4 2345 3 ASFD@INDIA.COM
                      E5 NAME5 1463 1 DWZS@GMAIL.COM
                      E6 NAME6 3444 1 ZGBA@YAHOO.COM
                       
                      
                      
                      i.e. for example as per the above data,
                      department manager 7 should recieve his employee records with all details
                      department manager 3 should recieve his employee records with all details
                      department manager 1 should recieve his employee records with all details
                      So are you planning to build a procedure that will take an input as DEPTMANAGER return the result set from the table for that DEPTMANAGER? or you are looking to sent email to all the DEPTMANAGER at a time? and how frequently this need to be done? You need to provide more detail on your requirement. And another thing the email ID provided in the example looks like that of the employees and not that of managers. Because manager 7 has two different email id. But to start with to get the employee details for a given DEPTMANAGER you can just write a simple SELECT statement with WHERE clause, something like this
                      select empno, ename, salary
                      from emp
                      where deptmanager = <your input>
                       
                      
                      And when some one ask for DB version (Which is a very critical piece of information) give it by querying the v$version table. Oracle DB version comes with 4 digits. 11g is not a version. 
                      
                      And finally i an not quite sure which annoys me the most, your arrogance or stupidity!!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                      • 8. Re: Help in a query please
                        Hoek
                        DatabaseAdministrator wrote:
                        I can understand how Hoek & sb92075 are making +1 to the number of posts by just commenting rather than giving proper answers. :-) So this is a good way of getting additional +1 to the number of posts you made. That is why some people never reach ace list yet have so many points, so sad.

                        My db version is 11g, any expert who can answer to my question. Thanks in advance.
                        I totally do not understand what you mean here. Your question was and still is incomplete and not clear at all, hence I pointed you to the SQL and PL/SQL FAQ.
                        For the record: participating on the forums has nothing to do with being/becoming an ACE.
                        My main motivation to participate here is to learn myself and learning through helping others (and have some fun now and then).
                        Points and/or the number of posts have no meaning (for several reasons that are way to offtopic and been discussed in the {forum:id=29} Forum) at all (to me, that is).
                        You should carefully read http://tkyte.blogspot.nl/2005/06/how-to-ask-questions.html as well...
                        Keep in mind that the way you posted your question here, would never have made it to being published on AskTom for example.

                        +1 for the other replies ;)
                        • 9. Re: Help in a query please
                          dba-india
                          Thank you all and sorry.