if else logic in vb6.0

Hi,
I need help in understanding logic
Case 1 number suppose 001 which has 2 records associated with it in database eample
pinno  address
1:       abc
1        dfg

I need to pick up abc or dfg on this criteria

need to check on table field
1st match :
scheme: 20 or 21 or smething
next match status : 'active' or 'inactive'
I abc has scheme neither 20,21  then i check dfg for scheme if it is neither 20,21 check for next match'status' for abc  and then dfg
Do it till someone is active or is 20 or 21
How do i go about it

RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mdouganCommented:
Hi RIAS,

Sounds like it shouldn't be hard to help you solve, but your explanation was incomprehensible.  If you want help with a SQL Query, then give the complete data structure of the tables involved, and then give a complete example of the expected output.  You give us 1: abc, 1 dfg and then start talking about scheme:20 or 21, what the heck is that?  

Give us the table structures, then a few sample rows of data for each table, then the expected results, and we can help you to get there.

Cheers!
0
RIASAuthor Commented:
Hi,
table PD
has
columns:
Field               type                allownull
Refno      varchar(10)       Unchecked
NINumber      varchar(11)      Checked
Scheme      varchar(4)    Checked
CurrStatus      varchar(50)      Checked



Need to find a refno for a ninumber which  has scheme '20' or '21' and currstatus='Active'

any query suggestions?

Cheers


0
mdouganCommented:
Sure, the SQL for that would be:

SELECT PD.refno
   FROM PD
WHERE currstatus = 'Active'
    AND  scheme IN ('20', '21')

alternatively, that last line could have been:

    AND  (scheme  = '20' OR scheme = '21')

But the IN clause is better in this case.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

RIASAuthor Commented:
Cheers mate for the help.But additional condition is that no if i get two refno from the table PD
who have cuurstatus active and scheme 20 or 21 .I need to select that refno who has scheme 20
is it possible
Cheers once again
0
mdouganCommented:
Let me see if I understand what you are saying.  let's say your PD table has the following data:

refno     ninumber     scheme    currstatus
1            aaaa            20             Active
2            aaaa            21             Active
3            bbbb            20             Inactive
4            bbbb            21             Active
5            cccc            21             Active
6            dddd            22             Active

Is it that you want the results to look like:
1           aaaa            20              Active
4           bbbb            21              Active
5           cccc            21              Active

In other words, you only want one result per NINumber, and you want the record with the scheme = 20 if it is active, or 21 if there is no 20, or if 20 is not active for that NINumber?
0
RIASAuthor Commented:
Specs are
select a refno based on following conditions
Case 1 scheme 20 or 21 and currstatus:'Active'  found then accept it
if case 1 fails then
Case 2  : check for scheme 20 or 21  
if case 1 and 2 fails
Case 3 :if scheme is some number than 20 or 21 check for currstatus 'Active'

Cheers
0
RIASAuthor Commented:
oh no the scene is I have only 1 NINumber with n number of refnos .
Need to select only one refno based on above cases
example:
ninumber    refno   scheme    currstatus
101             cca      20            Inactive
101             qae      21           Active

need to select refno qae as meets both the conditions scheme 20 and currstatus 'inactive

Case 2
ninumber    refno   scheme    currstatus
101             cca      1             Active
101             qae      21           Inactive

Selected refno is qae as first priority to scheme 20 or 21 than currstatus

Case 3
ninumber    refno   scheme    currstatus
101             cca      20            InActive
101             qae      21           Inactive
select cca as first priority to scheme 20 thna 21

based on this
first priority is scheme 20 or 21 and currstaus 'Active
no match
then check  scheme  and currstatus 'inactive
suppose case like

ninumber    refno   scheme    currstatus
101             cca      20            InActive
101             qae      21           Inactive
select cca as first priority to scheme 20 thna 21


Cheers





0
RIASAuthor Commented:
Sorry typo in the above post corrected version

Need to select only one refno based on above cases
example:
ninumber    refno   scheme    currstatus
101             cca      20            Inactive
101             qae      21           Active

need to select refno qae as meets both the conditions scheme 20 and currstatus 'active'

Case 2
ninumber    refno   scheme    currstatus
101             cca      1             Active
101             qae      21           Inactive

Selected refno is qae as first priority to scheme 20 or 21 than currstatus

Case 3
ninumber    refno   scheme    currstatus
101             cca      20            InActive
101             qae      21           Inactive
select cca as first priority to scheme 20 than  21

based on this
first priority is scheme 20 or 21 and currstaus 'Active
no match
then check  scheme  and currstatus 'inactive
suppose case like

ninumber    refno   scheme    currstatus
101             cca      20            InActive
101             qae      21           Inactive
select cca as first priority to scheme 20 thna 21


Cheers



0
mdouganCommented:
Are there going to be other NINumbers in the table?  In other words, are you trying to get one result per NINumber?  Can you tell me what database you are using?  SQL Server, Access?
0
RIASAuthor Commented:
Yes i need only one refno per ninumber depending on the above cases ..and if none of them match or data for both refno is same then i need to neglect that refno and in that case i have no refno for that ninumbe.
Yes I am using sql server.Working on it since yesterday very thankful to you as trying to help me
0
RIASAuthor Commented:
Using vb 6.0 recordsets for the above
0
mdouganCommented:
OK, here is my first try.  The only thing I'm a little worried about is that the query might not recognize TABLEB in the select statement where I'm doing the MIN... if not, I think I can get around it, but it is cleaner if this works.

Basically, what we're doing is creating a virtual table that includes the ninumber, scheme, and a priority number based on your criteria.  Anything that doesn't fit one of your criteria gets a NULL value as the priority, and should fall out of the query.  This is TABLEB.

We then select the refno, from the PD table, where the ninumber and scheme match the record from the virtual table (TABLEB), and where the priority from TABLEB is the lowest number for that ninumber. That should just give you one record for the ninumber.  Try it out and let me know what happens!

SELECT PD.ninumber, PD.refno
    FROM PD,
                (SELECT ninumber, scheme,
                CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                CASE WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                CASE WHEN scheme = '20' THEN 3
                CASE WHEN scheme = '20' THEN 4
                CASE ELSE THEN NULL
                END AS priority
                FROM PD) AS TABLEB
   WHERE PD.ninumber = TABLEB.ninumber
         AND PD.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT MIN(priority)
                                                          FROM TABLEB
                                                       WHERE TABLEB.ninumber = PD.ninumber)
0
mdouganCommented:
opps, spotted a typo:

SELECT PD.ninumber, PD.refno
    FROM PD,
                (SELECT ninumber, scheme,
                CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                CASE WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                CASE WHEN scheme = '20' THEN 3
                CASE WHEN scheme = '21' THEN 4
                CASE ELSE THEN NULL
                END AS priority
                FROM PD) AS TABLEB
   WHERE PD.ninumber = TABLEB.ninumber
         AND PD.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT MIN(priority)
                                                          FROM TABLEB
                                                       WHERE TABLEB.ninumber = PD.ninumber)
0
RIASAuthor Commented:
Cheers mate will try it and get back
0
RIASAuthor Commented:
Hi,
Tried the query got an error:syntax error  at CASE..cheers mate
0
mdouganCommented:
opps, try this:

SELECT PD.ninumber, PD.refno
    FROM PD,
                (SELECT ninumber, scheme,
                CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                          WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                          WHEN scheme = '20' THEN 3
                          WHEN scheme = '21' THEN 4
                          ELSE THEN NULL
                END AS priority
                FROM PD) AS TABLEB
   WHERE PD.ninumber = TABLEB.ninumber
         AND PD.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT MIN(priority)
                                                          FROM TABLEB
                                                       WHERE TABLEB.ninumber = PD.ninumber)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Hi mdougan,
First of all cheers for helping me..will try your suggestion but looking at the query I think i didn't mention that  I already have NInumber which i need not find from database i just need to match a valid refno for it from database depending on the cases mentioned.
In the above query i see no place where I can give my NInumber for the match of refno

Cheers
0
RIASAuthor Commented:
Hi,
Just tried the code the query :
SELECT PERSONAL_DETAILS.ninumber, PERSONAL_DETAILS.refno
    FROM PERSONAL_DETAILS,
                (SELECT ninumber, scheme,
                 CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                          WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                          WHEN scheme = '20' THEN 3
                          WHEN scheme = '21' THEN 4
                          ELSE  NULL
                END AS priority
                FROM PERSONAL_DETAILS) AS TABLEB
   WHERE PERSONAL_DETAILS.ninumber = TABLEB.ninumber
         AND PERSONAL_DETAILS.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT MIN(priority)
                                                          FROM TABLEB
                                                       WHERE TABLEB.ninumber = PERSONAL_DETAILS.ninumber)


Get an error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'TABLEB'.
0
mdouganCommented:
The error that you are getting above is the one I suspected we might get.  So, I've modified the query to hopefully get around it.  I also added a line in the where clause to compare the:

   WHERE PERSONAL_DETAILS.ninumber = @NINumber

In this way, if you pass the particular NINumber to the query as a parameter (in this case called @NINumber) then it would only look at those reccords in Personal_Details that have that NINumber... or, you could construct the SQL in your code and string in the value for the NINumber directly into the SQL.



SELECT PERSONAL_DETAILS.ninumber, PERSONAL_DETAILS.refno
    FROM PERSONAL_DETAILS,
                (SELECT ninumber, scheme,
                 CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                          WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                          WHEN scheme = '20' THEN 3
                          WHEN scheme = '21' THEN 4
                          ELSE  NULL
                END AS priority
                FROM PERSONAL_DETAILS) AS TABLEB
   WHERE PERSONAL_DETAILS.ninumber = @NINumber
         AND PERSONAL_DETAILS.ninumber = TABLEB.ninumber
         AND PERSONAL_DETAILS.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT MIN(
                                                                  CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                                                                            WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                                                                            WHEN scheme = '20' THEN 3
                                                                           WHEN scheme = '21' THEN 4
                                                                           ELSE  NULL
                                                                  END
                                                                    )
                                                FROM PERSONAL_DETAILS  X
                                                WHERE X.ninumber = PERSONAL_DETAILS.ninumber)
0
RIASAuthor Commented:
Solution provided is perfect in all respect..Excellent logic and a very helpful advisor.
0
RIASAuthor Commented:
Cheers mate you were a Angel
0
mdouganCommented:
My pleasure.

Cheers!
0
RIASAuthor Commented:
Hi,
Just spotted an case where it doesn't work.If you are available can you suggest
0
mdouganCommented:
absolutely
0
RIASAuthor Commented:
Cool
Here is the query
I am getting two results from the query

ninumber    refno      scheme          currstatus                                         priority
----------- ---------- ------ -------------------------------------------------- -----------
WMS          C70           20                  Preserved Pensioner                      3
WMS          C60          20                   Active                                             1
 
(2 row(s) affected)

Just need one result based on the priority.Here need refno c60 as priority 1
Modified the query you suggested on last two lines

Cheers mate




 
 
SELECT PERSONAL_DETAILS.ninumber, PERSONAL_DETAILS.refno,PERSONAL_DETAILS.scheme,PERSONAL_DETAILS.currstatus,tableb.priority
    FROM PERSONAL_DETAILS,
                (SELECT ninumber, scheme,
                 CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                          WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                          WHEN scheme = '20' THEN 3
                          WHEN scheme = '21' THEN 4
                          ELSE  NULL
                END AS priority
                FROM PERSONAL_DETAILS) AS TABLEB
   WHERE PERSONAL_DETAILS.ninumber = 'WMS'
         AND PERSONAL_DETAILS.ninumber = TABLEB.ninumber
         AND PERSONAL_DETAILS.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT min(
                                                                  CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                                                                            WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                                                                            WHEN scheme = '20' THEN 3
                                                                           WHEN scheme = '21' THEN 4
                                                                           ELSE  NULL
                                                                  END
                                                                    )
                                                FROM PERSONAL_DETAILS  X
                                                WHERE X.refno = PERSONAL_DETAILS.refno)

Open in new window

0
RIASAuthor Commented:
Hi mate,
Sorry bothered you again but found a solution.here is the changed query.
Cheers.
SELECT TOP 1 PERSONAL_DETAILS.ninumber, PERSONAL_DETAILS.refno,PERSONAL_DETAILS.scheme,PERSONAL_DETAILS.currstatus,tableb.priority
    FROM PERSONAL_DETAILS,
                (SELECT ninumber, scheme,
                 CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                          WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                          WHEN scheme = '20' THEN 3
                          WHEN scheme = '21' THEN 4
                          ELSE  NULL
                END AS priority
                FROM PERSONAL_DETAILS) AS TABLEB
   WHERE PERSONAL_DETAILS.ninumber = 'WMS'
         AND PERSONAL_DETAILS.ninumber = TABLEB.ninumber
         AND PERSONAL_DETAILS.scheme = TABLEB.scheme
         AND TABLEB.priority = (SELECT min(
                                                                  CASE WHEN currstatus = 'Active' AND scheme = '20' THEN 1
                                                                            WHEN currstatus = 'Active' AND scheme = '21' THEN 2
                                                                            WHEN scheme = '20' THEN 3
                                                                           WHEN scheme = '21' THEN 4
                                                                           ELSE  NULL
                                                                  END
                                                                    )
                                                FROM PERSONAL_DETAILS  X
                                                WHERE X.refno = PERSONAL_DETAILS.refno)
ORDER BY tableb.priority    
 
    
    

Open in new window

0
RIASAuthor Commented:
Hi,
Can I ask you one question if available?
0
mdouganCommented:
It wasn't clear to me if the scheme was going to be unique within an NINumber... I made the assumption it was, but your example showed it is not, but your solution was perfect, exactly the approach I would have taken.

By all means, ask away!  
0
RIASAuthor Commented:
Oh sorry to bother you again but it seems I have the solution by your guidance.
Cheers mate for giving me the right direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.