Changing a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing a query

Author

Topic

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-12-04 : 04:24:09

Hello,

I want to add a function 'initcap' into a query:

this is the query that works:

strSQL = " SELECT Personeelsnummer, Achternaam, Voornaam, "
strSQL = strSQL & " Begindatum, Einddatum, AantalWeken, Subsidie, Subsidie * AantalWeken AS Totaal "
strSQL = strSQL & " FROM "
strSQL = strSQL & " (SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, Premies.Begindatum, "
strSQL = strSQL & " Premies.Einddatum, PremiesOpleidingen.Subsidie, "
strSQL = strSQL & " CASE "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', Einddatum) "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, Einddatum) "
strSQL = strSQL & " ELSE 0 END AS AantalWeken, PremiesOpleidingen.Subsidiekeuze "
strSQL = strSQL & " FROM PremiesOpleidingen INNER JOIN "
strSQL = strSQL & " Premies ON PremiesOpleidingen.PremieID = Premies.PremieID INNER JOIN "
strSQL = strSQL & " Werknemers ON Premies.Personeelsnummer = Werknemers.Personeelsnummer) AS OVERZICHT "

This is the query that doesn't work with the initcap function:

strSQL = " SELECT Personeelsnummer, "
strSQL = strSQL & " dbo.InitCap (Achternaam) "
strSQL = strSQL & " + ' ' + ISNULL(LOWER(Werknemers.Tussenvoegsel), '') AS Achternaam, "
strSQL = strSQL & " LEFT(UPPER(Werknemers.Voornaam),1) + LOWER(RIGHT(Werknemers.Voornaam,LEN(Werknemers.Voornaam)-1)) AS Voornaam, "
strSQL = strSQL & " Begindatum, Einddatum, AantalWeken, Subsidie, Subsidie * AantalWeken AS Totaal "
strSQL = strSQL & " FROM "
strSQL = strSQL & " (SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, Premies.Begindatum, "
strSQL = strSQL & " Premies.Einddatum, PremiesOpleidingen.Subsidie, "
strSQL = strSQL & " CASE "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', Einddatum) "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, Einddatum) "
strSQL = strSQL & " ELSE 0 END AS AantalWeken, PremiesOpleidingen.Subsidiekeuze "
strSQL = strSQL & " FROM PremiesOpleidingen INNER JOIN "
strSQL = strSQL & " Premies ON PremiesOpleidingen.PremieID = Premies.PremieID INNER JOIN "
strSQL = strSQL & " Werknemers ON Premies.Personeelsnummer = Werknemers.Personeelsnummer) AS OVERZICHT "

I don't understand what is wrong with the query below?

Can somebody help me?

Thanks in advance,
Sven.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 05:36:03

quote:
This is the query that doesn't work with the initcap function

Doesn't work is not very clear for us!

Code of that function?
Wanted result?
Error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-12-04 : 05:51:22

Hello,

there is no error but I can't see any records anymore ...
the function initcap is working fine ... but I haven't got any idea why the select isn't working anymore.

Sven.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 06:42:53

Your SELECT goes on the derived table (inner select) with name OVERZICHT.
Your new SELECT wants "Werknemers.Tussenvoegsel" and "Werknemers.Tussenvoegsel" is not in OVERZICHT.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 06:43:49

same to Werknemers.Voornaam

No, you're never too old to Yak'n'Roll if you're too young to die.

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-12-04 : 07:24:17

Hello,

I don't understand why werknemers.voornaam is a problem?

It is present in both the select(s)

I change my query to this but it's still not working ...

strSQL = " SELECT Personeelsnummer, "
strSQL = strSQL & " dbo.InitCap (Achternaam) "
strSQL = strSQL & " + ' ' + ISNULL(LOWER(Werknemers.Tussenvoegsel), '') AS Achternaam, "
strSQL = strSQL & " LEFT(UPPER(Werknemers.Voornaam),1) + LOWER(RIGHT(Werknemers.Voornaam,LEN(Werknemers.Voornaam)-1)) AS Voornaam, "
strSQL = strSQL & " Begindatum, Einddatum, AantalWeken, Subsidie, Subsidie * AantalWeken AS Totaal "
strSQL = strSQL & " FROM "
strSQL = strSQL & " (SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Tussenvoegsel, Werknemers.Voornaam, Premies.Begindatum, "
strSQL = strSQL & " Premies.Einddatum, PremiesOpleidingen.Subsidie, "
strSQL = strSQL & " CASE "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum >= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', '" & Me.txteinddatum2 & "') "
strSQL = strSQL & " WHEN Begindatum <= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, '" & Me.txtbegindatum2 & "', Einddatum) "
strSQL = strSQL & " WHEN Begindatum >= '" & Me.txtbegindatum2 & "' AND Einddatum <= '" & Me.txteinddatum2 & "' THEN DATEDIFF(Week, Begindatum, Einddatum) "
strSQL = strSQL & " ELSE 0 END AS AantalWeken, PremiesOpleidingen.Subsidiekeuze "
strSQL = strSQL & " FROM PremiesOpleidingen INNER JOIN "
strSQL = strSQL & " Premies ON PremiesOpleidingen.PremieID = Premies.PremieID INNER JOIN "
strSQL = strSQL & " Werknemers ON Premies.Personeelsnummer = Werknemers.Personeelsnummer) AS OVERZICHT "
strSQL = strSQL & " WHERE begindatum <= '" & Me.txteinddatum2 & "' AND Einddatum >= '" & Me.txtbegindatum2 & "'"
strSQL = strSQL & " AND SubsidieKeuze = 2 "

Sven.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 07:51:27

Because your "outer select" has to take: voornaam or OVERZICHT.voornaam but not werknemers.voornaam

No, you're never too old to Yak'n'Roll if you're too young to die.

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-12-04 : 08:28:37

Hello,

indeed, this is working.
I now understand my mistake ...

Thanks for helping and tutoring me.

Sven.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 08:55:19