Start Free Trial

asked on

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

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!

ASKER

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

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.

ASKER

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

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?

ASKER

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

ASKER

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

ASKER

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

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?

ASKER

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

ASKER

Using vb 6.0 recordsets for the above

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)

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)

ASKER

Cheers mate will try it and get back

ASKER

Hi,
Tried the query got an error:syntax error at CASE..cheers mate

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

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'.

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)

ASKER

Solution provided is perfect in all respect..Excellent logic and a very helpful advisor.

ASKER

Cheers mate you were a Angel

My pleasure.

Cheers!

ASKER

Hi,
Just spotted an case where it doesn't work.If you are available can you suggest

absolutely

ASKER

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

ASKER

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

ASKER

Hi,
Can I ask you one question if available?

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!

ASKER

Oh sorry to bother you again but it seems I have the solution by your guidance.
Cheers mate for giving me the right direction.