Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everybody,
maybe someone help with the "IF NOT EXISTS" syntax. Going through the previous posts, I have noticed that this EXISTS command makes the assumption that the field must have the same name (or, did I get It wrong?). But this implies that the model will make an association, or even a concatenation. But instead, I want to keep the fields independent.
I have created a nice example about a dance competition. This example reproduces EXACTLY the business problem that I have to solve at work. Here it is.
THE PROBLEM:
There is a dance competition. Only the first 3 solo dancers (male or female) and the first 3 couples will win the admission to the final.
The solo competition is the most important. If a dancer is participating and winning in both, he will be automatically admitted to the solo final, and his couple will be eliminated.
The jury is issuing a ranking of the first 6 solo dancers and the first 6 couples. The task is to present the list of the 3 solo winners and the 3 couple winners, so that 9 distinct people can be sent to the final.
Here is the code:
SoloRanking:
LOAD * INLINE [
SoloRankNo, DancerNo
1, 15
2, 27
3, 33
4, 40
5, 41
6, 42
];
CoupleRanking:
LOAD * INLINE [
CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,
1, C03, 15, 50
2, C12, 51, 52
3, C15, 53, 54
4, C18, 55, 27
5, C31, 56, 57
6, C36, 58, 59
];
The desired output for the CoupleRanking is C12, C15, C31 (the couples C03 and C18 are eliminated, because one of the 2 dancers won the other competition)
My problem is how to discard the couples C03 and C18?!
Can anyone help?
Thanks,
Francesco
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
NOTE:
Without using the EXISTS command I have solved the problem as follows. But I am sure that there is a much more elegant solution..
MY TEMPORARY SOLUTION:
AllDancers:
LOAD
MaleDancerNo AS Dancer
RESIDENT
CoupleRanking;
Concatenate
AllDancers_concat:
LOAD
FeMaleDancerNo AS Dancer
RESIDENT
CoupleRanking;
LEFT JOIN (AllDancers)
DancersToExclude:
LOAD
DancerNo AS Dancer,
'Exclude' AS FlagToExclude
RESIDENT
SoloRanking;
ValidCoupleRanking:
LOAD
CoupleRankNo AS [Valid Couple Rank No],
CoupleNo AS [Valid Couple No],
MaleDancerNo AS [Valid Male Dancer],
FeMaleDancerNo AS [Valid Female Dancer]
RESIDENT
CoupleRanking;
INNER JOIN (ValidCoupleRanking)
ValidMaleDancers:
LOAD
Dancer AS [Valid Male Dancer],
'Valid Male' AS MaleDancerValidFlag
RESIDENT
AllDancers
WHERE LEN(FlagToExclude)=0
;
INNER JOIN (ValidCoupleRanking)
ValidFemaleDancers:
LOAD
Dancer AS [Valid Female Dancer],
'Valid Female' AS FemaleDancerValidFlag
RESIDENT
AllDancers
WHERE LEN(FlagToExclude)=0
;
DROP TABLE AllDancers;
Maybe like this?
SoloRanking:
LOAD * INLINE [
SoloRankNo, DancerNo
1, 15
2, 27
3, 33
4, 40
5, 41
6, 42
] where SoloRankNo <= 3;
CoupleRanking:
LOAD recno() as CoupleRankNoNew,* where recno() <=3;
LOAD *
INLINE [
CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,
1, C03, 15, 50
2, C12, 51, 52
3, C15, 53, 54
4, C18, 55, 27
5, C31, 56, 57
6, C36, 58, 59
]
where
not exists(DancerNo, MaleDancerNo)
and
not exists(DancerNo, FeMaleDancerNo)
;
Maybe like this?
SoloRanking:
LOAD * INLINE [
SoloRankNo, DancerNo
1, 15
2, 27
3, 33
4, 40
5, 41
6, 42
] where SoloRankNo <= 3;
CoupleRanking:
LOAD recno() as CoupleRankNoNew,* where recno() <=3;
LOAD *
INLINE [
CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,
1, C03, 15, 50
2, C12, 51, 52
3, C15, 53, 54
4, C18, 55, 27
5, C31, 56, 57
6, C36, 58, 59
]
where
not exists(DancerNo, MaleDancerNo)
and
not exists(DancerNo, FeMaleDancerNo)
;
Excellent, thanks a lot!
This is exactly what I was looking for.
Now that you have sent me this example, I finally managed to understand the syntax and the example on the ref manual.
Basically, the thing that was not obvious to me, is that the [Expression] can also be a name of a different field,
Thanks a lot
F.