[thelist] SQL Statement Problems

Jonathan_A_McPherson at rl.gov Jonathan_A_McPherson at rl.gov
Wed Jul 31 11:22:00 CDT 2002


Jay,

You're right -- SQL is the appropriate place for the kind of filtering logic
you're talking about.

Add this stuff to your WHERE clause.

AND (

> 1. If instPayStatus1 <> 'paid' no other status exists

(instPayStatus1 <> 'paid' AND instPayStatus2 IS NULL AND instPayStatus3 IS
NULL) OR

> 2. If instPayStatus1 = 'paid' and there exists a instPayStatus2 <> 'paid'
>    no other status exists

(instPayStatus1 = 'paid' AND instPayStatus2 IS NOT NULL AND instPayStatus2
<> 'paid' AND instPayStatus3 IS NULL) OR

> 3. If instPayStatus1 = 'paid' and there exists a instPayStatus2 = 'paid'
>    and there exists a instPayStatus3 <> 'paid'

(instPayStatus1 = 'paid' AND instPayStatus2 = 'paid' AND instPayStatus3 IS
NOT NULL AND instPayStatus3 <> 'paid')

)

> I was hoping that typing out the thought would bring me to a realization,
> but alas I find it does not.

<tip type="Working out problems" author="jeduthun">
Stuck on some logical problem you *know* you should be able to figure out,
but just can't? Try composing an e-mail to someone that thoroughly describes
the problem and the solutions you have tried. Or call your co-worker over
and try to verbally explain the problem.

You'll be amazed at how many times you will be able to solve the problem
before you finish the e-mail or conversation. When I was working on my
bachelor's degree, I composed about twice as many e-mails to professors than
I actually sent, because I so often realized what was wrong with my approach
as I was attempting to describe it thoroughly for someone else.
</tip>

--
Jonathan McPherson, LMIT/SD&I
Software Engineer & Web Systems Analyst
email / jonathan_a_mcpherson at rl dot gov
phone / 509.373.0150

-----Original Message-----
From: Jay Blanchard [mailto:jay.blanchard at niicommunications.com]
Sent: Wednesday, July 31, 2002 8:52 AM
To: thelist at lists.evolt.org
Subject: [thelist] SQL Statement Problems


SELECT s.btn, s.custName, t.instPayDate1, t.instPayAmt1, t.instPayStatus1,
t.instPayDate2, t.instPayAmt2,                         	 t.instPayStatus2,
t.instPayDate3, t.instPayAmt3, t.instPayStatus3
FROM tblSubscriber s, tblTracking t
WHERE t.btn = s.btn
AND s.btn = s.wtn
AND s.billStatus <> 'I'
AND t.instPayDate1 IS NOT NULL
AND t.instPayAmt1 > 0.01

This is the current statement, but I need to now further narrow the list
returned, which I can do with PHP. I think that I should be able to do it
with SQL, which is why I ask. This affects the instPayStatus fields;

The following should be included in the return;

1. If instPayStatus1 <> 'paid' no other status exists
2. If instPayStatus1 = 'paid' and there exists a instPayStatus2 <> 'paid'
   no other status exists
3. If instPayStatus1 = 'paid' and there exists a instPayStatus2 = 'paid'
   and there exists a instPayStatus3 <> 'paid'

I was hoping that typing out the thought would bring me to a realization,
but alas I find it does not.

TIA!

Jay

I've run out of sick days, so I'm calling in dead



More information about the thelist mailing list