Må ikke en skid i mssql'en

hvad med

SELECT Url AS Url2
FROM KALENDER_EVENTS
GROUP BY Url

?

[generelt mener jeg at man kan mere i MS SQL end i MySQL]

Ok, det var forsimplet for meget ;-)

Jeg vil gerne klippe lidt i url'en, som den så skal gruppere efter:

SELECT replace(Url, 'http://','') AS Url2
FROM KALENDER_EVENTS
GROUP BY Url2

Og det er stadig skrevet for nemhedens skyld (da mit virkelig script, hvor jeg har problemet, er blevet kæmpe stort).

SELECT replace(Url, 'http://','') AS Url2
FROM KALENDER_EVENTS
GROUP BY replace(Url, 'http://','')

og jeg har set hvordan din query ser ud i andre spørgsmål

jeg tror at du skal reorganisere lidt

evt. flytte logik far databasen til applikationen

Jeg har allerede logik'en i en vb-funktion, men jeg har netop forsøgt, at få den ind i sql'en for overskuelighedens skyld... men hvad f... hjælper det, når jeg skal gentage logik-syntaxen... helt op til 3 gange i min kode.

Nå, jeg må hellere gøre, som erikjacobsen anbefaler i: http://www.eksperten.dk/spm/639898

Altså at smække en ekstra tabel ind, som jeg fylder dataen ind i, og senere, når select-sætningen er relevant, henter dataen derfra. Det er desværre en to-trins-raket... havde håbet på at kunne fyre den helt til Mars i et hug.

Nå, så kom jeg til vejs ende - ville have det til at virke, får jeg går over til tidligere omtalte løsning med en ekstra tabel.

Det virker, jo jo, men hold da helt ferie, hvor er det grimt. 4 gange har jeg måtte bruge sorterings-funktionen.

function cleanUrl(str)

    cleanUrl = "LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(" & str & ".URL, 7 + CHARINDEX('/', SUBSTRING(" & str & ".URL + '/', 8, LEN(" & str & ".URL) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', ''))"

end function

function siteDropdown()
                         
strSQL = "SET DATEFORMAT DMY SELECT " & cleanUrl("E") & " AS NEWURL, COUNT(D.DATE) AS COUNTER" & vbNewLine & _
"FROM KALENDER_EVENTS E LEFT OUTER JOIN" & vbNewLine & _
"        KALENDER_DATES D ON E.ID = D.EventID AND (D.[Date] >= '01/" & navmonth & "/" & navyear & "') AND (D.[Date] <= '01/" & navmonthTemp & "/" & navyearTemp & "')" & vbNewLine & _
"WHERE (LEN(E.Url) > 5) AND (" & cleanUrl("E") & ") IN (" & vbNewLine & _
"        SELECT " & cleanUrl("P") & vbNewLine & _
"      FROM PLUSPROFIL_ANNONCE P" & vbNewLine & _
"      WHERE (P.UsedPoints < P.OrderPoints) AND LEN(P.URL) > 5)" & vbNewLine & _
"GROUP BY " & cleanUrl("E") & vbNewLine & _
"ORDER BY COUNTER DESC"

Bemærk, at jeg slutter sætningen af med at lave en order by på counter (som jeg undfangede oppe ved select), og som fint virker. Havde man nu også kunne gøre det samme med group by så var koden blevet meget kønnere.

Hvorfor ikke en sub-select:

SELECT * FROM
(
SELECT replace(Url, 'http://','') AS Url2
FROM KALENDER_EVENTS
)s1
GROUP BY s1.Url2

Go ide... kigger på det nu :-)

Ok, men hvordan indsætter jeg det nu? Den fejler:

SELECT    COUNT(D.DATE) AS COUNTER
FROM        (LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.URL, 7 + CHARINDEX('/', SUBSTRING(E.URL + '/', 8, LEN(E.URL) - 6)) - 1), ',', '.'), 'https://', ''),
                      'http://', ''), 'www.', '')) AS NEWURL FORM KALENDER_EVENTS) L KALENDER_EVENTS E LEFT OUTER JOIN
                      KALENDER_DATES D ON E.ID = D.EventID AND (D.[Date] >= '01/8/2005') AND (D .[Date] <= '01/9/2005')
WHERE    L.NEWURL LIKE
                          (SELECT    TOP 1 '%' + P.URL + '%'
                            FROM          PLUSPROFIL_ANNONCE P
                            WHERE      (P.UsedPoints < P.OrderPoints))
GROUP BY L.NEWURL
ORDER BY COUNTER DESC

Med andre ord, så kan jeg se, at du i dit eksempel placerer subselect'en efter from-delen, men hvordan skal jeg lige gøre det i mit tilfælde, når nu jeg joiner mv?

Nå, jeg glemte lige at indsætte ordet select i min subselect... ret væsentligt :-)

Men den fejler stadig (fordi jeg ikke kan finde ud af at placere subselect'en korrekt) - den siger: Incorrect syntax near FROM

SELECT    COUNT(D .DATE) AS COUNTER
FROM        (SELECT    LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.URL, 7 + CHARINDEX('/', SUBSTRING(E.URL + '/', 8, LEN(E.URL) - 6)) - 1), ',', '.'),
                                              'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL FORM KALENDER_EVENTS) L KALENDER_EVENTS E LEFT OUTER JOIN
                      KALENDER_DATES D ON E.ID = D.EventID AND (D.[Date] >= '01/8/2005') AND (D.[Date] <= '01/9/2005')
WHERE    L.NEWURL LIKE
                          (SELECT    TOP 1 '%' + P.URL + '%'
                            FROM          PLUSPROFIL_ANNONCE P
                            WHERE      (P.UsedPoints < P.OrderPoints))
GROUP BY L.NEWURL
ORDER BY COUNTER DESC

Hvis jeg nu analyserer din funktion (lad os kalde den URLdomain), så er trinene (fra inderst mod yderst:

1) SUBSTRING(E.URL + '/', 8, LEN(E.URL) - 6)
2) CHARINDEX('/', "1)")
3) LEFT(E.URL, 7 + "2)" - 1)
4) REPLACE("3)", ',', '.')
5) REPLACE("4)", 'https://', '')
6) REPLACE("5)", 'http://', '')
7) REPLACE("6)", 'www.', '')
8) LOWER("7)"

For mig at se burde du have vendt proceduren om, så du starter med at konvertere til lowercase, fjerne https://, http:// og www. og herefter smide alt fra og med det første / tegn væk.

Det gør naturligvis LEFT funktionen lidt mere kompliceret, fordi du nu er nødt til at gentage de tidligere funktioner i LEN.

LEFT(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(E.URL + '/'), 'www.', ''), 'http://', ''), 'https://', ''), ',', '.'), CHARINDEX('/',REPLACE(REPLACE(REPLACE(REPLACE(LOWER(E.URL + '/'), 'www.', ''), 'http://', ''), 'https://', ''), ',', '.')) - 1)

Jeg har set bort fra SUBSTRING funktionen, da jeg ikke har kunnet gennemskue, hvad det skulle bruges til...

Takker - jeg mener at substring skal bruges til at klikke det væk, som kommer efter slashen... altså "/bib/side.htm". Men er ikke sikker. Uanset hvad, hvis du kan lave en bedre funktion, så er det bare super.

Men tilbage til rahps forslag - 14/08-2005 08:35:20.

Det skal indbygges en ekstra subselcte, som kun har til formål, at jeg kan genbruge NEWURL hele vejen ned gennem sætningen, så jeg ikke skal gentage den 3 gange.

Her kommer min senste kode, som virker. Jeg har ikke indsat din ombyggede-replace-funktion endnu:

SELECT LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL, COUNT(D.[Date]) AS COUNTER
FROM KALENDER_EVENTS E LEFT OUTER JOIN KALENDER_DATES D ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT P.URL
    FROM PLUSPROFIL_ANNONCE P
    WHERE (P.UsedPoints < P.OrderPoints) AND P.URL LIKE '%' + LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', UBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '') + '%')) AND (LEN(E.Url) > 5
  )
GROUP BY LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', ''))
ORDER BY COUNTER DESC

Tja, så kunne det jo være noget lignende dette:

SELECT NEWURL, COUNT(D.[Date]) AS COUNTER
FROM
  (SELECT LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL
  FROM KALENDER_EVENTS ) AS E
LEFT OUTER JOIN KALENDER_DATES D
  ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT P.URL
  FROM PLUSPROFIL_ANNONCE P
  WHERE (P.UsedPoints < P.OrderPoints)
    AND P.URL LIKE '%' + E.NEWURL + '%'
    AND LEN(E.URL) > 5
  )
GROUP BY NEWURL
ORDER BY COUNTER DESC

Ovenstående er med din oprindelige funktion. Jeg tror bare, at du vil få problemer, hvis URL f.eks. er skrevet med store bogstaver, men det kan du jo hurtigt finde ud af.

Ahh, jeg ser lige, at E.ID indgår i conditionen for det outer join til KALENDER_DATES, så det skal naturligvis med også:

SELECT NEWURL, COUNT(D.[Date]) AS COUNTER
FROM
  (SELECT ID, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL
  FROM KALENDER_EVENTS ) AS E
LEFT OUTER JOIN KALENDER_DATES D
  ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT P.URL
  FROM PLUSPROFIL_ANNONCE P
  WHERE (P.UsedPoints < P.OrderPoints)
    AND P.URL LIKE '%' + E.NEWURL + '%'
    AND LEN(E.URL) > 5
  )
GROUP BY NEWURL
ORDER BY COUNTER DESC

... og du tester også på længden på den oprindelige URL, så endnu en ændring er vel på sin plads:

SELECT NEWURL, COUNT(D.[Date]) AS COUNTER
FROM
  (SELECT ID, URL, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL
  FROM KALENDER_EVENTS ) AS E
LEFT OUTER JOIN KALENDER_DATES D
  ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT P.URL
  FROM PLUSPROFIL_ANNONCE P
  WHERE (P.UsedPoints < P.OrderPoints)
    AND P.URL LIKE '%' + E.NEWURL + '%'
    AND LEN(E.URL) > 5
  )
GROUP BY NEWURL
ORDER BY COUNTER DESC

Mange tak, spændende og se, hvordan koden bliver mere hyggelig ;-)

Desværre siger den: "The column prefix 'E' does not match with a table name or alias name used in the query."

Arne, noget som du kan fixe (mens kjulius er offline)?

der er

... ON E.ID = ...

den brokekr sig over - og E er defineret her

...) AS E LEFT ...

har du kopieret koden helt som den står her ?

Ja, præcist, men Enterprise fjerner "AS" og kommer med omtalte prefix-fejl.

Før kørsel i enterprise:
... FROM KALENDER_EVENTS) AS E LEFT OUTER JOIN...

Efter kørsel:
... FROM KALENDER_EVENTS) E LEFT OUTER JOIN...

Men her smider jeg hele koden, så I kan se det (måske har Enterprise ændret på mere end blot as-delen):

SELECT NEWURL, COUNT(D.[Date]) AS COUNTER
FROM (SELECT ID, URL, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL
FROM KALENDER_EVENTS) E LEFT OUTER JOIN KALENDER_DATES D ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT    P.URL
  FROM      PLUSPROFIL_ANNONCE P
  WHERE      (P.UsedPoints < P.OrderPoints) AND P.URL LIKE '%' + E.NEWURL + '%' AND LEN(E.URL) > 5)
GROUP BY NEWURL
ORDER BY COUNTER DESC

AS er optional

men jeg tror jeg har fundet den

det er ikke hvor jeg treoede det var

inde i queryen som definerer E bruger du E. - det skal være original tabelnavn

REPLACE(LEFT(E.Url, 7 + CHARINDEX('/', SUBSTRING(E.Url + '/', 8, LEN(E.Url) - 6)

kan ikke bruge E.

Ahhh, men det er jo nemt :-) Så virker det. Mange, mange, mange, mange, mange tak for hjælpe alle :-) Tryk svar alle :-)

Koden er følgende:

SELECT E.NEWURL, COUNT(D.[Date]) AS COUNTER
FROM (SELECT ID, URL, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(Url, 7 + CHARINDEX('/', SUBSTRING(Url + '/', 8, LEN(Url) - 6)) - 1), ',', '.'), 'https://', ''), 'http://', ''), 'www.', '')) AS NEWURL
FROM KALENDER_EVENTS) E LEFT OUTER JOIN    KALENDER_DATES D ON E.ID = D.EventID AND D.[Date] >= '01/8/2005' AND D.[Date] <= '01/9/2005'
WHERE EXISTS
  (SELECT    P.URL
    FROM      PLUSPROFIL_ANNONCE P
    WHERE      (P.UsedPoints < P.OrderPoints) AND P.URL LIKE '%' + E.NEWURL + '%' AND LEN(E.URL) > 5)
GROUP BY E.NEWURL
ORDER BY COUNTER DESC

Kunne man ikke flytte replace-monsteret ud i en decideret sql-funktion, så koden bliver mere overskuelig? Og jeg tænker ikke på en vb-læsning, men findes der ikke en måde, som man kan oprette en sql-funktion på?

læsning = løsning

man kan godt lave FUNCTION's i T-SQL

eksempel copy pastet fra BOL:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
  (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
  RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

og et svar fra mig

Ok, takker, hvordan skal jeg få min replace-ting ind i den?

du laver vel en funktion med url som input og newurl som output ?

Ok, prøver at rode med det senere. Men hvor skal sql-funktionen ligge - i asp-filen (sammen med sql-koden) eller som logik i mssql-serveren (og i så fald kan det indsættes fra enterprise)?

den laves på SQLServeren f.eks. i EM med en CREATE FUNCTION kommando

Takker, og så kan jeg bare kalde den direkte fra sql'en (vb-koden) ved f.eks. at skrive monsterReplace(E.URL)?

Og vil afviklingen af sql'en bliver hurtigere, når jeg anvender sådanne sql-funktioner eller vil det være det samme?

ja (bortset fra at E. vist stadig ikke er valid i konteksten)

svært at sige - jeg vil formode at den bliver hurtigere, men jeg tør ikke
gætte på om det er meget eller lidt

ok, takker :-)

Godt der var hjælp at hente. Jeg var desværre ikke lige ved skærmen, da det brændte på.

:-)

Tryk også svar, rahp

Svar (har dog ikke bidraget med meget)