• Status: Solved
• Priority: Medium
• Security: Public
• Views: 679

# 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
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

0
RIAS
• 18
• 11
1 Solution

Commented:
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

Author 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

Commented:
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

Author 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

Commented:
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

Author 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

Author 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

Author 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

Commented:
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

Author 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

Author Commented:
Using vb 6.0 recordsets for the above
0

Commented:
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

Commented:
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

Author Commented:
Cheers mate will try it and get back
0

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

Commented:
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

Author 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

Author 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

Commented:
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

Author Commented:
Solution provided is perfect in all respect..Excellent logic and a very helpful advisor.
0

Author Commented:
Cheers mate you were a Angel
0

Commented:
My pleasure.

Cheers!
0

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

Commented:
absolutely
0

Author 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)
``````
0

Author 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

``````
0

Author Commented:
Hi,
Can I ask you one question if available?
0

Commented:
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.

0

Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.