Oracle Query #3

To simplify things, let's say my query is joining three tables: Table_A, Table_B, and Table_C.

Table_A contains activity information.

Table_B contains user information.

Table_C contains course information.

When a user in Table_B accesses a course in Table_C, a row gets written into Table_A with an event type of 'COURSE_ACCESS' and a timestamp. If a user does not access a course, no rows exist in Table_A that contains an event type of 'COURSE_ACCESS'.

I want to return the latest timestamp for a given user in a given course. My query does this, but if a user does not have a course access row in Table_A, that user and course information does not get returned. I want it to appear in my report along with a blank or null for the timestamp. Here's what I have so far:

SELECT b.LastName, b.FirstName, c.Course_ID, c.Course_Name, MAX(a.Timestamp) AS Timestamp
FROM
Table_B b
INNER JOIN Table_A a ON b.PK1 = a.User_PK1
INNER JOIN Table_C c ON a.Course_PK1 = c.PK1
WHERE b.User_ID = 'someID' AND c.Course_ID = 'someID' AND a.Event_Type = 'COURSE_ACCESS'
GROUP BY b.LastName, b.FirstName, c.Course_ID, c.Course_Name

I've tried using a right and left outer joins but my results are the same. How can I get those rows to still return?


if (myPost == Problem.Answer) myPost.MarkedAsAnswer = true;
0
bhuber7
4/29/2009 5:28:34 PM
asp.net.oracle 2269 articles. 0 followers. Follow

2 Replies
448 Views

Similar Articles

[PageSpeed] 7

Please try:

SELECT b.LastName, b.FirstName, c.Course_ID, c.Course_Name, MAX(a.Timestamp) AS Timestamp
FROM Table_B b,Table_A a,Table_C c
WHERE a.User_PK1 = b.PK1(+) AND a.Course_PK1 = c.PK1(+)
      AND b.User_ID = 'someID' AND c.Course_ID = 'someID'
      AND a.Event_Type = 'COURSE_ACCESS'
GROUP BY b.LastName, b.FirstName, c.Course_ID, c.Course_Name


"Object reference not set to an instance of an object"
0
che3358
4/29/2009 6:14:09 PM

You can also use outer join (like table_a(+)) and decode (that can check if something can be retrieved from table_a or not) to take that.

Syntax

Description of decode.gif follows
Description of the illustration decode.gif

Purpose

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.

  • If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as the first result parameter.

  • If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.


Many Thanks & Best Regards,
HuaMin Chen

(Mark it as answer if it does help you!)
0
wmec
4/30/2009 4:10:10 AM
Reply:

Similar Artilces:

Where can I get native PB drivers for ( Oracle Version 8.0.0.5) with Oracle Net Client Version 7.3.3
( Oracle Version 8.0.0.5) with Oracle Net Client Version 7.3.3 with SQL *Net version 2.x , 32 bit implementation. All versions of PB Enterprise (5,6,7) have the native O73 driver. If you have PB Professional, you can only use ODBC to connect to Oracle. -- Terry Dykstra (TeamSybase) Canadian Forest Oil Ltd. Check out Sybase Developer's Network: http://www.sybase.com/sdn Veena Madhavi <bveenamadhavi@hotmail.com> wrote in message news:38A8FD91.6218D227@hotmail.com... > ( Oracle Version 8.0.0.5) with Oracle Net Client Version 7.3.3 with SQL > *Net version 2...

urgent: .NET Provider for Oracle or Oracle Data Provider for .NET
If I want to access Oracle DB in ASP.NET application, and I am using .NET Provider for Oracle or Oracle Data Provider for .NET, should I have to install OracleClient on my .NET server? Is there other choice? Thank you very much.Johnson Johnson2007:should I have to install OracleClient on my .NET server?  You have to options1. install the ODP.NET on your web serverOR2. put the oracle assemblies in your BIN directory   Hi jimmy,Thank you for you reply. Because I could not find a server right now, I should test it first before I agree or disagree with you, as most articles I...

Connecting PB 5.0.03 to Oracle 8 (NT) through SQL Net v2 with driver for Oracle 7.3 of PB
Can anyone assist me on Connecting PB 5.0.03 to Oracle 8 (NT) through SQL Net v2 with driver for Oracle 7.3 of PB ? Thank you very much in advance. -- John Fotiadis Direct +30 94-464225 Senior Systems Engineer Relational Technology S.A. In article <34979B76.67A813A8@sybase.com.gr>, johnf@sybase.com.gr says... > Can anyone assist me on Connecting PB 5.0.03 to Oracle 8 (NT) through > SQL Net v2 with driver for Oracle 7.3 of PB ? > > Thank you very much in advance. > > -- > John Fotiadis > Direct +30 94-464225 > Senior Systems Enginee...

.NET Oracle Provider
Hi all,I am using .NET 2.0 Frameworks Oracle provider and it does not have a OracleDbTypes Type for me to use. I would like to create a parameter that is of the Oracle type NVARCHAR, but it seems I can only use DbTypes, which does not have this. If I used DbTypes.String, would this be ok, otherwise, how else can I be more specific about the Oracle Parameter data type?Thanks  can u explain which type of data u r going to storeThank uBaba Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. What's your version of ODP? You have OracleD...

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

Connecting to Oracle 7.3 and Oracle 8
Hi, We've developed several PB applications that use Oracle 7.3. Now, we're moving some of the development to Oracle 8. We're trying to figure out how to set up developers' machines in the interim so that all applications will function normally. We have to decide which version of SQL*Net to use and which PB database interface for each application. Can we use different SQL*Net and interface on the same computer? So far, I can only use one version of SQL*Net during runtime (whichever one comes first in the system path). Also, during runtime, the PB database interface...

Oracle ODP.net mulitple Oracle Client
To connect to the oracle database using ODP.net, the applicate must add the dll file called Oracle.DataAccess. There are a lot of version of Oracle.DataAccess. As I know, if the client use Oracle Client 10g Release 2. If the client use Oracle Client 10g Release 2, the application also have to use the Oracle.DataAccess the come from Oracle Client 10g Release 2.  My question is that there are any method to add more than one Oracle.DataAccess version in my product to support multiple OracleClient.   That should automatically happen for the most part.  Oracle's ODP.NET i...

DBD::Oracle with Oracle 10.1.3
------=_NextPart_000_001A_01C94667.A691AB30 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I have new Oracle version 10.1.3 in sun4-solaries machine. My DBD:Oracle version is 1.14. My cgi-perl applications were working fine with oracle 9g. After getting oracle 10g, I changed DBD:Oracle Makefile.pl and run make and make install commands. Did not have any problem during compilation. But my cgi-perl applications do not work and I get the following error in log file install_driver(Oracle) failed: Can't load '/usr/local/lib/perl5/...

Which .Net Provider for Oracle 9i Microsoft or Oracle
Can anyone provide me pros and cons of provider to access Oracle Database ? I am aware of Microsoft Provider and Oracle Provider. Microsoft http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp Oracle http://otn.oracle.com/software/tech/windows/odpnet/utilsoft.html I guess you can use either. But as some post suggests, if both installed, then MS ODP will not work. I've been using DataDirect Technologies Connect for .Net and Oracle. It's not free, so it depends on your budget. The speed improvements were great for us, and it's ni...

which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...

DBD::Oracle (perl 5.8.0, Oracle 8.0.3, DBD-Oracle 1.12, Solaris 8) make problems (fprintf)
The only fprintf warning I googled was at http://archive.develooper.com/dbi-announce@perl.org/msg00081.html Any suggestions appreciated. I'm assuming since the perl Makefile.PL ran clean, I'm using kosher Oracle libraries (binaries worked with Perl 5.6.0). Thanks, Dave S. Appended output of (in order): make output perl Makefile.PL perl -V gcc -v >make gcc -c -I/app/oracle/product/8.0.3/rdbms/demo -I/app/oracle/product/8.0.3/rdbms /public -I/app/oracle/product/8.0.3/plsql/public -I/app/oracle/product/8.0.3/net work/public -I/app/oracle/product/8.0....

Do I need to install both oracle 7.3 and oracle 8 clients to work my PB applications some with ora 7.3 and some ora 8?
Hi, My PB5 application uses Oracle 7.3 and PB8 applications uses oracle 8i. Do I need to install both Oracle 7.3 and Oracle 8 clients to deploy my PB 5 and PB 8 application. Please comment Thanks Mulinti I've answered this elsewhere. -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. SDN: http://www.sybase.com/developer/ http://www.pb9books.com <Mulinti> wrote in message news:D3D0347141F544BB0059FE7585256CBD.0059FE8585256CBD@webforums... > Hi, > > My PB5 application uses Oracle 7.3 and PB8 applications uses oracle 8i....

how to implement login module using Oracle 10g database (logic & Oracle 10g Queries) and how can i use membership and roles with Oracle 10g & VS 2.0
 Hi all, Can any one tell me how to implement login module using Oracle 10g database (logic & Oracle 10g Queries) and how can I use membership and roles with Oracle 10g & vs 2.0. , for implement this is it compulsory to install oracle 10g server on the same machine on which I'm implementing this task  or otherwise i can use  other system as a server (like remote login or client server)  Hi,You should have the Oracle 10g Server or Client In your machine.In Case Client is in your Machine then Server Must be somewhere,that is enough to use.I hope then we ...

Replicating Oracle to Oracle
How do I use replication server to replicate between two oracle databases when some of the tables to be replicated has no primary key or unique index constraint? We'll apreciate your contributions. Femi Olumofin Covenant Visions Int'l +234-1-4976764 In Sybase, you can create clustered index on the unique key in the table and use the unique key as your primary key in the replication definition. It should work in oracle as well since that functionalilty is built in repserver and not Oracle. Bawoni Femi Olumofin wrote: > How do I use replication server to re...

Web resources about - Oracle Query #3 - asp.net.oracle

Oracle - Wikipedia, the free encyclopedia
This article is about the classical medium. For the software company, see Oracle Corporation . For other uses, see Oracle (disambiguation) . ...

Oracle - Wikipedia, the free encyclopedia
This article is about the classical medium. For the software company, see Oracle Corporation . For other uses, see Oracle (disambiguation) . ...

News Oracle
The News Oracle is an online news outlet. The Oracle covers the most relevant stories from around the globe.

Oracle Acquires Facebook Data Partner Datalogix
One of Facebook’s data partners has a new owner, as Datalogix was acquired by Oracle , the two companies announced Tuesday. (more…) New Career ...

With AddThis Buy, Oracle Gets Pipeline to Continually Updated Audience Data
Tech giant Oracle on Tuesday said it had struck a deal to acquire AddThis, a maker of tools to easily let consumers share web pages on social ...

Android N switches to OpenJDK, Google tells Oracle it is protected by the GPL
The Oracle v. Google legal battle over the use of Java in Android keeps on going, but this week Google made a change to Android that it hopes ...

Oracle: Morgan Stanley Cuts Target as Cloud Eats into Their Margin
Morgan Stanley ’s Keith Weiss this morning reiterates an Equal Weight rating on shares of Oracle ( ORCL ) following last week’s fiscal Q2 earnings ...

Oracle agrees to warn Java users of malware risk
Oracle is about to issue a warning that Java users could be exposed to malware, the media have reported on Tuesday. The exposure is the result ...

Oracle Corporation (ORCL) Upgraded to Buy at Evercore ISI
Analysts at Evercore ISI upgrade Oracle stock to Buy on the back of attractive valuation.

Google comes up with a new Android plan as its epic legal battle with Oracle continues
Oracle and Google have been duking it out for years in a lawsuit that could have a big impact on the whole software industry. Now Google has ...

Resources last updated: 1/17/2016 10:57:51 AM