Report Data

Results 1 to 4 of 4

Thread: Report Data

  1. #1
    Monica Stone Guest

    Default Report Data

    I&#039;m making a web-application, and have a table similar to tblEmployee in Oracle with addition of 2-fields: HireDate, Sex(M/F), Department(A,B,C,D)(There is no other table for "Department").<BR><BR>Want to produce reports in this format:(For Dept-A, Manager-Sex-M, Employee-Sex-M)<BR><BR>ManagerName, HireDate<BR>EmployeeName, HireDate, Job<BR><BR>The output will look like this:<BR>ManagerName1, HireDate<BR>EmployeeName1, HireDate, Job<BR>EmployeeName2, HireDate, Job<BR>EmployeeName3, HireDate, Job<BR><BR>ManagerName2, HireDate<BR>EmployeeName1, HireDate, Job<BR>EmployeeName2, HireDate, Job<BR><BR>ManagerName3, HireDate<BR><BR>ManagerName4, HireDate<BR>EmployeeName1, HireDate, Job<BR>EmployeeName2, HireDate, Job<BR><BR>ie. List a manager with his/hers subordinates, then jump to next manager. The list would be sorted by EName(goes for both Manager and employees)<BR><BR>Been told by DBA, not to use Datashaping. (Dont know the exact reason, probably missing the correct drivers)<BR><BR>Suggested by a colleague to use:<BR>SELECT col_name, col_name, .....<BR>FROM emp<BR>WHERE esex=&#039;M&#039; AND edept=&#039;A&#039;<BR>CONNECT BY PRIOR empno = mgr<BR>ORDER BY ename<BR><BR>Just dont know how the above would help to acheive the desired output.<BR><BR>I&#039;m currently doing(There has to be a smarter way:<BR><BR>strSQL = "SELECT eno, ename, ehiredate FROM emp WHERE ejob=&#039;MANAGER&#039; AND esex=&#039;M&#039; ORDER BY ename"<BR><BR>Do Until rsManager.EOF<BR><BR>rsManager(1), rsManager(2)<BR><BR>strSQL1 = "SELECT ename, ehiredate, ejob FROM emp WHERE ejob!=&#039;MANAGER&#039; AND esex=&#039;M&#039; AND mgr=rsManager(0) ORDER by ename"<BR><BR>Do until rsEmployee.EOF<BR>rsEmployee(0), rsEmployee(1), rsEmployee(2)<BR>rsEmployee.MoveNext<BR>loop<BR>rs Manager.MoveNext<BR>loop<BR><BR>The above-stmts might not be accurate but it gives you experts the idea<BR><BR>Need your help.<BR>

  2. #2
    Heaven's Martini Guest

    Default oracle is great i am sure

    but why can&#039;t you use<BR>a group by.<BR><BR>Don&#039;t know if there is one in Oracle<BR><BR>I would group by manager id,<BR><BR>the query may be a bit difficult.<BR><BR>else<BR>i would but the data in an array (GetRows)<BR>and then just set a trigger = the manager name<BR>if trigger = name print employee,<BR>if trigger &#060;&#062; name print manager header.<BR><BR>its really pretty simple and tons faster than using the <BR>recordset method. Although a stored procedure would be even better, but thats another story.

  3. #3
    Monica Stone Guest

    Default RE: oracle is great i am sure --- Newbie

    I&#039;m new with SQL and Oracle.<BR>Sample-code would very helpful

  4. #4
    Heaven's Martini Guest

    Default Next.

    Well<BR><BR>I am beyond new with Oracle.<BR><BR>You could say I am pre-virgin, in other words, I only use Sql server, (and access).<BR><BR>sorry. One of these asp jedis probably has oracle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts