CASE statement help with multiple tables

[kbsc]

NEED SOME HELP!! I've created the query below. I need a query that will look in a table and if intCaseID is null, then will pull a date in another table, else will pull a date in that table.

Right now it only works one way if the value is not null, it works correctly. If its null, then it doesn't pull the value in t.dteTransaction.

SELECT
(CASE when d.intCaseID is null then MAX(t.dteTransaction)
ELSE MAX(d.dtePosted) END) As dte
FROM tblTransaction t
INNER JOIN
tblBankAccount b
ON b.intAccountID = t.intAccountID
INNER JOIN
tblDistribution d
ON d.intCaseID = b.intCaseID

GROUP BY d.intCaseID, b.intCaseID, d.intPosted, b.intDeleted, d.intDeleted, t.intDeleted,
t.intTransactionMethod

HAVING
(b.intCaseID = 468267)
AND d.intPosted = 1
AND b.intDeleted = 0
AND d.intDeleted = 0
AND t.intDeleted = 0
AND t.intTransactionMethod = 124

 

[kaht]

use coalesce and put the columns in the order you'd like it to check first:

coalesce(firstColumnToCheck, secondColumnToCheck, thirdColumnToCheck, etc..)

coalesce returns the first non-null value in the parameter list, or null if they are all null.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[gmmastros]

Kaht,

That's not going to work in this situation.

Notice that if Col1 is null, return Col2, otherwise return Col3.

Coalesce would return firstColumnToCheck when it is not null instead of the other column.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

You're right, I only read 1/2 of the question and assumed I knew where the poster was going with the question. But, maybe I'm lucky and they still learned something

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[ESquared]

[s][red]INNER JOIN
tblDistribution d[/red][/s]

LEFT JOIN
tblDistribution d

 

[kbsc]

I did learn something, but I still can't get my query to work. I think its the multiple joins. The COALESCE could work but its only functioning like my original query.

It will pull a value when d.dtePosted is not null, which is correct, BUT its not pulling a value when d.dtePosted is NULL and then needs to look in t.dteTransaction and pull the top 1.

Here's my new query with the COALESCE but I still need someone's help.

SELECT
TOP 1 COALESCE(d.dtePosted, t.dteTransaction) As dte
FROM tblBankAccount b
Left JOIN
tblTransaction t
ON b.intAccountID = t.intAccountID
Left JOIN
tblDistribution d
ON b.intCaseID = d.intCaseID

WHERE
(d.intCaseID = 468503)
AND d.intPosted = 1
AND b.intDeleted = 0
AND b.intAccountType = 17
AND d.intDeleted = 0
AND t.intDeleted = 0
AND t.intTransactionMethod = 124

 

[kbsc]

I rewrote it another way and I'm still getting the same results as the one above.

ANY suggestions PLEASE!!

SELECT
TOP 1 (CASE when d.intCaseID is null then
(SELECT MAX(t.dteTransaction) FROM tblTransaction t
INNER JOIN tblBankAccount b ON b.intAccountID = t.intAccountID
GROUP BY t.intAccountID, t.intDeleted, b.intDeleted, t.intTransactionMethod, b.intAccountType, b.intCaseID
HAVING t.intDeleted = 0 AND b.intDeleted = 0 AND b.intAccountType = 17
AND b.intCaseID = 468503)

ELSE (SELECT MAX(d.dtePosted) FROM tblDistribution d
GROUP BY d.intDeleted, d.intPosted, d.intCaseID
HAVING d.intDeleted = 0 AND d.intCaseID = 468503 AND d.intPosted = 1)

END) As dte
FROM tblDistribution d
WHERE d.intCaseID = 468503

 

[gmmastros]

Can you show some sample data from the tables that you are using in the query. Also, show what the expected result it. That will make it easier for us to help you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kbsc]

Ok

For lets say b.intCaseID = 1

in tblDistribution there's a row that meets the criteria of intDeleted = 0 and intPosted = 1, so it should pull the tblDistribution.dtePosted value of 1/1/2007

For lets say b.intCaseID = 2

in tblDistribution there are no rows for intCaseID = 2. So I want the query to look in tblTransaction where intTransactionType = 124 and intDeleted = 0 and then pull the MAX(tblTransaction.dteTransaction), which would be 2/24/2007.

All of my queries right now, pull the correct value for intCaseID = 1, but it pulls no value for intCaseID = 2. So that's where my problem is.

Let me know if that makes sense, or if you need more details.Thanks so much.

 

[gmmastros]

Can you write 2 seperate queries. One for each case. One that pulls the data from the tblDistribution table and another that pulls the data from the tblTransaction table. Post both queries here, and I will show you how to combine them so that you get the results you are looking for.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kbsc]

OK first this query needs to be runned and produce a value if there is a record in tblDistribution that meets the parameters.

SELECT TOP 1(d.dtePosted) FROM tblDistribution d
WHERE d.intDELETED = 0 AND d.intPosted = 1 AND d.intCaseID = @intCaseID

If there was not a record in tblDistribution that meets the parameters or is null, then this query needs to be run.

SELECT TOP 1(t.dteTransaction) FROM tblTransaction t
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
WHERE b.intCaseID = @intCaseID AND b.intDeleted = 0 AND t.intDeleted = 0
AND b.intAccountType = 17 AND t.intTransactionMethod = 124

If there's at least one record in tblTransaction that meets that criteria then pull the dteTransaction value TOP 1.

If no records meet either 2 of the queries parameters then no value should be returned.

 

[kbsc]

I forgot to mention its very important that only one date value is produced. This query can't have multiple values as a result.

 

[gmmastros]

Hmm..... Try this.

Select Coalesce(dist.dtePosted, Trans.dteTransaction) As SomeDate
From   (
       SELECT TOP 1 (t.dteTransaction),
              intCaseId
       FROM   tblTransaction t
              INNER JOIN tblBankAccount b 
                 ON t.intAccountID = b.intAccountID
       WHERE  b.intCaseID = @intCaseID 
              AND b.intDeleted = 0 
              AND t.intDeleted = 0
              AND b.intAccountType = 17 
              AND t.intTransactionMethod = 124
       Order By dteTransaction DESC
       ) As Trans
       Left Join (
          SELECT TOP 1 (d.dtePosted),
                 intCaseId
          FROM   tblDistribution d
          WHERE  d.intDELETED = 0 
                 AND d.intPosted = 1 
                 AND d.intCaseID = @intCaseID
          Order By dtePosted DESC
          ) as Dist
          On Trans.intCaseId = Dist.intCaseID

There is a potential problem here. Whenever you use TOP in a query, you should include an Order By. If you don't, there is no guarantee that you will get the right record/value.

I took the liberty of adding order by clauses. Of course, I am making an assumption here, so you may need to correct that.

Anyway... give this a try. If this query works properly and you would like further explanation, let me know. It's important that you understand how this works.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kbsc]

Your code works perfect. I'll have to troubleshoot it with every scenario, but it looks like exactly what I need.

I think I follow the logic, I just didn't know that could be done.

The select COALESCE part is naming the two values i want pulled (first column dist.dtePosted if not null it'll pull this value, second column Trans.dteTransaction it'll pull this value)

Then the two select statement go into detail of how to pull the value for the COALESCE.

In the 2 select statements, why are they LEFT JOINED??

Also, in the COALESCE statement, if both columns are null, can I add a third one with a default value of 1/1/1900, which would mean no value found for this intCaseID.

THanks so much!!

 

[gmmastros]

First let me explain a little about this query. It uses "derived tables". This is a technique that allows you to have a query within a query. It's similar to subqueries, but is actually different.

Each of the queries you posted became derived tables. You can recognize derived table by noticing the parenthesis around a query and an Alias after it.

      (
      Select blah from whooo
      ) As AliasName

>> In the 2 select statements, why are they LEFT JOINED??

With an Inner Join, you will only get records when there is a match in both tables. With a Left Join, you will get records from the left table, and any records that match in the RIGHT table. There are some implications here that you need to consider. First of all, realize that I am actually talking about the derived tables (including the where clause).

Most importantly, though. If the 'left' table doesn't include any rows, then there will not be any rows returned from the right table. So, in this case, if there are not any records in the tblTransaction table, then there will not be any records pulled from the tblDistribution table (even if they exist).

>> Also, in the COALESCE statement, if both columns are null, can I add a third one with a default value of 1/1/1900, which would mean no value found for this intCaseID.

Usually, this is true. However, since you won't get any records, then the Coalesce statement wouldn't set the value anyway. Make sense? Besides, what is the point of setting the date to 1/1/1900? So you can tell if there isn't any records? In this case, returning null would have the same effect, right?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kbsc]

You're right the 1/1/1900 is a default in our system that the field doesn't have a date value set. The null value thats returned will act as the same for me in this situation.

Thanks for explaining about why you used the Left Join instead of Inner Join.

Hopefully I'll be able to use this technique in future queries. Thanks so much!!

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom