SQL Error 512 Subquery returned more than 1 value. This is not permitted when the sub (1 Viewer)

T

Tiger955

Registered User.
Local time
Today, 11:42
Joined
Sep 13, 2013
Messages
140
Hi!

I have a huge problem with this SP:

I nedd to get the first time (earliest time) togehter with some other field from a table by using a subquery.

I get the error above, but I cannot modify the query to get my result.

Please be so kind and help me with this subqueries:

Code: 
SELECT DISTINCT tblPersonal.SMSNummer, tblEventPositionen.Nachname, tblEventPositionen.Vorname, tblEventPositionen.Nachname + ' ' + tblEventPositionen.Vorname AS Mitarbeiter, tblEventPositionen.Barcode, 
Min(tblEventPositionen.Einsatzzeitvon) AS Einsatzzeitvon, Max(tblEventPositionen.Einsatzzeitbis) AS Einsatzzeitbis, 
tblEventPositionen.MaID, 
(SELECT Kurzcode From tblEventPositionen as Q where Evid=@Evid and tblEventPositionen.Barcode = Q.barcode and Q.Einsatzzeitvon=Min(tblEventPositionen.Einsatzzeitvon)) AS Kurzcode, 
(SELECT EvLocBerID From tblEventPositionen as Q2 where Evid=@Evid and tblEventPositionen.Barcode = Q2.barcode and Q2.Einsatzzeitvon=Min(tblEventPositionen.Einsatzzeitvon)) AS EvLocBerID, 
tblEventPositionen.GeschlechtText, 
tblEventPositionen.Geschlecht, 
tblPersonal.RegID
FROM (tblEventPositionen INNER JOIN tblPersonal ON tblEventPositionen.MaID = tblPersonal.MaID) INNER JOIN tblEventAnmeldungen ON tblPersonal.MaID = tblEventAnmeldungen.MaID
WHERE (((tblEventAnmeldungen.Status)='OK') AND ((tblEventAnmeldungen.EvID)=@Evid) AND ((tblEventPositionen.EvID)=@Evid))
GROUP BY tblPersonal.SMSNummer, tblEventPositionen.Nachname, tblEventPositionen.Vorname, tblEventPositionen.Barcode, tblEventPositionen.MaID, tblEventPositionen.GeschlechtText, tblEventPositionen.Geschlecht, tblPersonal.RegID
HAVING (((Len([tblPersonal].[SMSNummer]))>0));

Employees are set to certain positions in an event. Each position may have a different start time and a different end time.
Employees can start on a position A at 14:00 and change later to anothere position B at 16:00. At position A his "Kurzcode" is "AT" and at position B it is "BC".

I need to know the EARLIEST time per employee and the the "Kurzcode" and the "EvLocBerID" for this time for each employee.

I cannot use First and Last as this result could give me the first and the last entered record which may not be the earliest time he is on duty!!

Thanks a lot, as I cannot find a solution for that.

Michael
 
Rx_

Rx_

Nothing In Moderation
Local time
Today, 03:42
Joined
Oct 22, 2009
Messages
2,803
Don't see anyone else responding and don't have much time to look at the details.
suggest you break down the query into components.
Make a copy to test. First get rid of the two Sub (Select...) queries and see if it runs.
Next get rid of the GroupBy - yes this will disable the min and max for now.
Basically, bread this down into steps and find where this error begins.
In its present state, it is too complicate to venture much more than a guess.

In a nutshell, I typically start very simple and add one ingredent to my recipe at a time.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 11:42
Joined
Aug 11, 2003
Messages
11,695
one of your inline selects (which is a bad thing to do anyways) is returning more than one record, that is what the error is telling you.
 
T

Tiger955

Registered User.
Local time
Today, 11:42
Joined
Sep 13, 2013
Messages
140
@RX
You are right, my sql is much to difficult to understand for another person not knowing my construction.

Meanwhile I solved the problem by using a scalar UDF for my subqueries and it works.

Thanks!
Michael
 
Rx_

Rx_

Nothing In Moderation
Local time
Today, 03:42
Joined
Oct 22, 2009
Messages
2,803
That is great. I am doing this myself more often and seeing wonderful results. Especially when there is logic involved. It is much faster than using an immediate Iff of function in Access.

Since you went this route, can you publish your UDF code then show how it was called in SQL? e.g. Select (UDF).

I would like to encourage more people regarding this subject. Helping others sometimes helps me learn more.
 
T

Tiger955

Registered User.
Local time
Today, 11:42
Joined
Sep 13, 2013
Messages
140
Well Rx, here is the way which made it work for me:
This is one of my UDFs, I made two of them, the same way:

Code: 
[COLOR=blue][FONT=Consolas]CREATE FUNCTION[/FONT][/COLOR][COLOR=teal][FONT=Consolas][dbo][/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas][sfFindEvLocBerID][/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR]
[COLOR=green][FONT=Consolas]-- Add the parameters for the function here[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]@Evid [/FONT][/COLOR][COLOR=blue][FONT=Consolas]int[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]@Barcode [/FONT][/COLOR][COLOR=blue][FONT=Consolas]nchar[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=black][FONT=Consolas]11[/FONT][/COLOR][COLOR=gray][FONT=Consolas]),[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]@Einsatzzeitvon [/FONT][/COLOR][COLOR=blue][FONT=Consolas]datetime[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas])[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]Returns nchar[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=black][FONT=Consolas]10[/FONT][/COLOR][COLOR=gray][FONT=Consolas])[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]BEGIN[/FONT][/COLOR]
[COLOR=green][FONT=Consolas]-- Declare the return variable here[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]DECLARE[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@EvLocBerID[/FONT][/COLOR][COLOR=blue][FONT=Consolas]asint[/FONT][/COLOR][COLOR=gray][FONT=Consolas];[/FONT][/COLOR]
[COLOR=green][FONT=Consolas]-- Add the T-SQL statements to compute the return value here[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]SELECT[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@EvLocBerID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEvpos[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]EvLocBerID[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]From[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEvPos[/FONT][/COLOR][COLOR=blue][FONT=Consolas]WHERE[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEvPos[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Evid[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@EvID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]and[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEvPos[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas]and[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEvPos[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Einsatzzeitvon[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Einsatzzeitvon[/FONT][/COLOR]
[COLOR=green][FONT=Consolas]-- Return the result of the function[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]RETURN[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@EvLocBerID[/FONT][/COLOR][COLOR=gray][FONT=Consolas];[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]END[/FONT][/COLOR]
Here is my stored procedure, using the UDF's
Code: 
[COLOR=blue][FONT=Consolas]CREATE PROCEDURE[/FONT][/COLOR][COLOR=teal][FONT=Consolas][dbo][/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas][spCheckInTimePersonal][/FONT][/COLOR]
[COLOR=green][FONT=Consolas]-- Add the parameters for the stored procedure here[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]@Evid[/FONT][/COLOR][COLOR=blue][FONT=Consolas]int[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]BEGIN[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]SETNOCOUNTON[/FONT][/COLOR][COLOR=gray][FONT=Consolas];[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]SELECTDISTINCT[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]SMSNummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Nachname[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Vorname[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Nachname[/FONT][/COLOR][COLOR=gray][FONT=Consolas]+[/FONT][/COLOR][COLOR=red][FONT=Consolas]' '[/FONT][/COLOR][COLOR=gray][FONT=Consolas]+[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Vorname[/FONT][/COLOR][COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Mitarbeiter[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=fuchsia][FONT=Consolas]Min[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Einsatzzeitvon[/FONT][/COLOR][COLOR=gray][FONT=Consolas])[/FONT][/COLOR][COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR][COLOR=teal][FONT=Consolas]CheckinZeit[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=fuchsia][FONT=Consolas]Max[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Einsatzzeitbis[/FONT][/COLOR][COLOR=gray][FONT=Consolas])[/FONT][/COLOR][COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR][COLOR=teal][FONT=Consolas]CheckoutZeit[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=blue][FONT=Consolas]select[/FONT][/COLOR][COLOR=teal][FONT=Consolas]dbo[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]sfFindKurzcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Evid[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=fuchsia][FONT=Consolas]Min[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Einsatzzeitvon[/FONT][/COLOR][COLOR=gray][FONT=Consolas])))[/FONT][/COLOR][COLOR=blue][FONT=Consolas]as[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Kurzcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas],  --here is UDF1[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=blue][FONT=Consolas]select[/FONT][/COLOR][COLOR=teal][FONT=Consolas]dbo[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]sfFindEvLocBerID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Evid[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=fuchsia][FONT=Consolas]Min[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Einsatzzeitvon[/FONT][/COLOR][COLOR=gray][FONT=Consolas])))[/FONT][/COLOR][COLOR=blue][FONT=Consolas]AS[/FONT][/COLOR][COLOR=teal][FONT=Consolas]EvLocBerID[/FONT][/COLOR][COLOR=gray][FONT=Consolas], -- here is UDF2[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]GeschlechtText[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Geschlecht[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR]
[COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]RegID[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]FROM [/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas]INNERJOIN[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=blue][FONT=Consolas]ON[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR][COLOR=gray][FONT=Consolas])INNERJOIN[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventAnmeldungen[/FONT][/COLOR][COLOR=blue][FONT=Consolas]ON[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventAnmeldungen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]WHERE [/FONT][/COLOR][COLOR=gray][FONT=Consolas]((([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventAnmeldungen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=blue][FONT=Consolas]Status[/FONT][/COLOR][COLOR=gray][FONT=Consolas])=[/FONT][/COLOR][COLOR=red][FONT=Consolas]'OK'[/FONT][/COLOR][COLOR=gray][FONT=Consolas])AND(([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventAnmeldungen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]EvID[/FONT][/COLOR][COLOR=gray][FONT=Consolas])=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Evid[/FONT][/COLOR][COLOR=gray][FONT=Consolas])AND(([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]EvID[/FONT][/COLOR][COLOR=gray][FONT=Consolas])=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Evid[/FONT][/COLOR][COLOR=gray][FONT=Consolas]))[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]GROUPBY[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]SMSNummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Nachname[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Vorname[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Barcode[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]MaID[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]GeschlechtText[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblEventPositionen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Geschlecht[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblPersonal[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]RegID[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]HAVING [/FONT][/COLOR][COLOR=gray][FONT=Consolas]((([/FONT][/COLOR][COLOR=fuchsia][FONT=Consolas]Len[/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas][tblPersonal][/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas][SMSNummer][/FONT][/COLOR][COLOR=gray][FONT=Consolas]))>[/FONT][/COLOR][COLOR=black][FONT=Consolas]0[/FONT][/COLOR][COLOR=gray][FONT=Consolas]));[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]END[/FONT][/COLOR]
Uups, I just saw that all blanks inside the code disappeard. Please think about that.

HTH
Michael


 
Last edited:
N

namliam

The Mailman - AWF VIP
Local time
Today, 11:42
Joined
Aug 11, 2003
Messages
11,695
Using UDFs to solve this problem is like shooting a musquito with a cannon.

I really do not expect you need a UDF at all here... particular since its all a simple select on tblEventPositionen
Why do you feel you cannot solve this in plain SQL? What is your requirement that is so complex that this is not possible or prefered?
 
T

Tiger955

Registered User.
Local time
Today, 11:42
Joined
Sep 13, 2013
Messages
140
Well, I could not get to the Min(EinsatzZeitvon) (=start time of work for the employee) in which Teams he starts and the Max(Einsatzzeitbis) (=end time of his work).

The employee has maybe two times.
he starts at 09:30 in Team A until 12:00 and from 12:00 to 16:00 in Team B.

How to find his earliest work time and what that Team is he?
Same with another field value EvLocBerID (=Location ID).

What would be your suggestion to get the results?
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 11:42
Joined
Aug 11, 2003
Messages
11,695
You are already grouping by barcode ( = team? )

Why not simply do Min(Einsatzzeitvon) inside the query?
Or if need be in a subselect... instead of in a UDF.
 
T

Tiger955

Registered User.
Local time
Today, 11:42
Joined
Sep 13, 2013
Messages
140
No, every employee has his Barcode. So barcode=MaID.

I could not define a subquery (subselect) therefore I took a UDF, but this was not my preferred solution.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom