ASP SQL select statement

[Adelbert]

Hallo,

Ik heb een probleempje bij een select statement uit een sql database. Het probleem is als volgt:

Ik wil een selectie maken uit het klantenbestand van mensen die in deze maand 10 worden dus 10, 20,30 enz. Het probleem is dat alle data die ik nodig heb ik 1 blok staan, dus als volgt 19800101henk. (geboortejaar,maand,dah,roepnaam)

Normaal gesproken los ik dit op door bv: right(waarde,2) enz te gebruiken, echter werkt dat niet in deze situatie.

Hoe kan ik nou of daar nog een selectie op los laten of dit in een keer op lossen?


Hopelijk is het duidelijk.

[Woy]

Hier in PRG zien we graag wat meer eigen inzet, zo willen we graag weten wat je allemaal al geprobeerd hebt en wat daar niet aan lukte.

Lees ook even PRG beleid en Quickstart door.

Verder denk ik dat je eerst eens naar je data-model moet kijken. Het is niet handig om je data op deze manier op te slaan, zoals je zelf ook al ondekt hebt.

Waarom sla je de verschillende data niet gewoon in aparte columns op?

[Niemand_Anders]

Waarom doe je de presentatie van de vier velden niet gewoon op de (ASP) server?
Dan blijft je query simpel en leesbaar (onderhoudbaar) en blijft de presentatie waar hij hoort.

[Adelbert]

Ik heb al verschillende dingen geprobeerd zoals zoeken met LIKE, verder loop ik hierop vast daarom heb ik niets in mijn openingspost hierover vermeld. Ik heb niet zelf de database opgezet anders had ik het wel anders ingedeeld. Het is een applicatie die aan een bestand pakket hangt.

Ik zit al te denken om alle klantgegevens over te pompen naar een wel goed ingedeelde DB.

[Woy]

Adelbert schreef op donderdag 02 april 2009 @ 09:33:
Ik heb al verschillende dingen geprobeerd zoals zoeken met LIKE, verder loop ik hierop vast daarom heb ik niets in mijn openingspost hierover vermeld.

Doe dat dan voortaan a.u.b. wel, wij hebben immers geen glazen bol. Ook al is het mischien niet de juiste richting om in te zoeken, we weten dan ieder geval wel wat je geprobeerd hebt, en wat daar niet aan lukte.

Ik heb niet zelf de database opgezet anders had ik het wel anders ingedeeld. Het is een applicatie die aan een bestand pakket hangt.

Dan zou ik toch eens met de makers van dat pakket praten, en ze proberen te overtuigen dat het niet handig is. Maar helaas heb je af en toe te maken met dergelijke situaties waar je niet veel aan kunt doen.

Ik zit al te denken om alle klantgegevens over te pompen naar een wel goed ingedeelde DB.

Als je niet altijd de meest up-to date gegevens hoeft te hebben kan dat inderdaad een optie zijn.

Maar als ik goed begrijp wat je wilt is dat je iedere persoon wilt hebben waar de string begint met bijvoorbeeld 198001?

Dan doe je toch gewoon zoiets

SQL:

SELECT  *
FROM     Personen
WHERE  VeldMetAlleGegevens LIKE '198001%'

[Adelbert]

Oke dit gedeelte werkt:

SQL:

SELECT  *
FROM     Personen
WHERE  VeldMetAlleGegevens LIKE '%19__"&maand&"%'

Nu ben ik nog aan het stoeien met de leeftijd. Er staat geen kolom leeftijd in de db, en ook geen data over de leeftijd in de blokdata.

[Woy]

Ten eerste moet je rekening houden met slechte performance als je een wildcard aan het begin van een LIKE vergelijking zet ( Er is dan verplicht een Full Table Scan nodig ).

Verder lijkt me dat er wel gegevens over de leeftijd in de column staat. Het jaartal/maand staat er toch in? Dan weet je dus ook de leeftijd.

Let je overigens ook op SQL Injection? Ik weet niet waar "maand" vandaan komt, maar als dat aan de hand van user-input is, dan moet je die wel goed controleren.

[Adelbert]

De wildcard aan het begin is wel nodig omdat de datum ergens midden in de regel staat.

Het is natuurlijk mogelijk om de leeftijd uit te laten rekenen (wat ik ookal gedaan heb), maar dan heb ik niet alleen de 10 leetijden zoals 10,20,30 enz.

Is aan de hand van user input, maar is verplichte keuze en goed gecontroleerd.

[Woy]

Adelbert schreef op donderdag 02 april 2009 @ 10:26:
De wildcard aan het begin is wel nodig omdat de datum ergens midden in de regel staat.

Het is natuurlijk mogelijk om de leeftijd uit te laten rekenen (wat ik ookal gedaan heb), maar dan heb ik niet alleen de 10 leetijden zoals 10,20,30 enz.

Je hoeft de leeftijd toch niet uit te rekenen? Je weet de leeftijden al, je kan daarbij de juiste jaartallen/maanden berekenen.

[Adelbert]

Ja zo kan het ook, maar hoe kan ik hier dan een mooi overzicht van krijgen?

Nu heb ik alle mensen die jarig zijn in de maand april in een lijst.

[Woy]

Als je alle mensen wilt hebben die deze maand 10, 20 en 30 worden doe je toch hetvolgende

SQL:

SELECT  * 
FROM     Personen 
WHERE  VeldMetAlleGegevens LIKE '%199904%'
OR          VeldMetAlleGegevens LIKE '%198904%'
OR          VeldMetAlleGegevens LIKE '%197904%'

[Matis]

Woy schreef op donderdag 02 april 2009 @ 10:36:
Als je alle mensen wilt hebben die deze maand 10, 20 en 30 worden doe je toch hetvolgende

SQL:

SELECT  * 
FROM     Personen 
WHERE  VeldMetAlleGegevens LIKE '%199904%'
OR          VeldMetAlleGegevens LIKE '%198904%'
OR          VeldMetAlleGegevens LIKE '%197904%'

Dat zou ook kunnen, persoonlijk snap ik al niet waarom er een VeldMetAlleGegevens is, maar dat terzijde.

Dan zal ik voor de zekerheid wel vanuit gaan dat de mensen 150 jaar worden

Daarnaast zal dit je performance wel enorm onderuit halen...

[Woy]

toaomatis schreef op donderdag 02 april 2009 @ 10:39:
[...]
Dat zou ook kunnen, persoonlijk snap ik al niet waarom er een VeldMetAlleGegevens is, maar dat terzijde.

Woy schreef op donderdag 02 april 2009 @ 09:28:
Verder denk ik dat je eerst eens naar je data-model moet kijken. Het is niet handig om je data op deze manier op te slaan, zoals je zelf ook al ondekt hebt.

Waarom sla je de verschillende data niet gewoon in aparte columns op?

en

Adelbert schreef op donderdag 02 april 2009 @ 09:33:
Ik heb niet zelf de database opgezet anders had ik het wel anders ingedeeld. Het is een applicatie die aan een bestand pakket hangt.

Ik zit al te denken om alle klantgegevens over te pompen naar een wel goed ingedeelde DB.

toaomatis schreef op donderdag 02 april 2009 @ 10:39:
Daarnaast zal dit je performance wel enorm onderuit halen...

Woy schreef op donderdag 02 april 2009 @ 10:15:
Ten eerste moet je rekening houden met slechte performance als je een wildcard aan het begin van een LIKE vergelijking zet ( Er is dan verplicht een Full Table Scan nodig ).

Dus dat is allemaal al gezegd in dit topic.

[ Voor 3% gewijzigd door Woy op 02-04-2009 10:43 ]

[Matis]

Woy schreef op donderdag 02 april 2009 @ 10:43:
Dus dat is allemaal al gezegd in dit topic.

Ok, mijn verontschuldiging.

Wat je misschien ook zou kunnen doen is een maal per maand checken wie er jarig is/wordt de komende maand. Dat zal je iig een hele hoop tijd schelen voor de rest van de bezoekers.

Eventueel zal je deze jarigen in een database kunnen schieten en mocht er een nieuwe gebruiker aanmelden EN hij wordt deze maand 10 jaar, dan kun je hem er eventueel nog bijzetten.

[Adelbert]

Ja, oke .

Ik ben nu aan het stoeien met probleem nr2, er staan namelijke meerdere data in de kolom.

edit: het ook de bedoeling dat dit maar 1x per maand gechecked wordt.
edit2: dit probleem opgelost door standaard vaste waarde die voor de geb datum staat mee te nemen in het select statement

[ Voor 51% gewijzigd door Adelbert op 02-04-2009 11:03 ]

[Adelbert]

Alles functioneerd. Er is echter nog 1 punt wat wel erg handig zou zijn en waar ik wederom wel wat tips voor kan gebruiken.

Ik heb nu een nette lijst maar ik wil de datums oplopend sorteren. Normaal gesproken gebruik je dan gewoon ORDER BY, maar in deze situatie werkt dat niet omdat de datum ergens midden in het tekstblok staat.

Iemand enig idee over een goed work around ?

[Woy]

Iets met ORDER BY gecombineerd met een substring

[Adelbert]

Een substring werkt toch alleen als volgt:

stringObject.substring(start,stop) ?

De posities zijn variabel. De enige vaste waarde die ik heb ik het label voor de datum (is altijd hetzelfde nummer).

[gorgi_19]

Adelbert schreef op vrijdag 03 april 2009 @ 13:03:
Een substring werkt toch alleen als volgt:

stringObject.substring(start,stop) ?

Nee, je moet wel de SQL variant pakken.

http://www.google.nl/sear...q=SQL+Server+Substr&meta=

De posities zijn variabel. De enige vaste waarde die ik heb ik het label voor de datum (is altijd hetzelfde nummer).

Daar heb je dingen onderstaand voor, waarmee je de positie bepaald
http://msdn.microsoft.com...ary/ms186323(SQL.90).aspx

[ Voor 31% gewijzigd door gorgi_19 op 03-04-2009 13:18 ]

[Adelbert]

Wat ik nu heb is dit:

SQL:

SELECT *
FROM naw
WHERE blok LIKE '%0000"&j10&""&maand&"%' OR...... enz
ORDER BY SUBSTR(blok,charindex('0000',blok))

0000 = voorliggende code
blok = kolomnaam

edit: alleen krijg ik de melding: Unexpected extra token

[ Voor 29% gewijzigd door Adelbert op 03-04-2009 15:21 ]

[bastv]

ASP:

WHERE blok LIKE '%0000"&j10&maand&"%' OR...... enz 

?

[Adelbert]

Om dat deel gaat het niet

Het draait nu alleen nog om de ORDER BY regel.

[Woy]

Wat is er niet duidelijk aan de documentatie?

SUBSTRING ( expression , start , length )

[Adelbert]

Niets, enkel dat ik deze foutmelding krijg [AcuCorp, Inc.][AcuODBC Driver]Unexpected extra token.

edit: kan die substring rechtstreeks in de order by regel of kan het beter op deze manier:

SQL:

SELECT somefield, SUBSTRING( somefield, LENGTH( somefield ) , 1 ) AS orderfield
FROM `bugreport` 
WHERE 1 
ORDER BY `orderfield` ASC

[ Voor 56% gewijzigd door Adelbert op 06-04-2009 09:13 ]

[Woy]

Je houdt je hier ecter niet aan de method signature van SUBSTRING

Adelbert schreef op vrijdag 03 april 2009 @ 15:16:

SQL:

SELECT *
FROM naw
WHERE blok LIKE '%0000"&j10&""&maand&"%' OR...... enz
ORDER BY SUBSTR(blok,charindex('0000',blok))

Dat zou dus je foutmelding kunnen verklaren.

Adelbert schreef op maandag 06 april 2009 @ 09:03:
Niets, enkel dat ik deze foutmelding krijg [AcuCorp, Inc.][AcuODBC Driver]Unexpected extra token.

edit: kan die substring rechtstreeks in de order by regel of kan het beter op deze manier:

Volgens mij mag je volgens de SQL specificatie niet orderen op een column alias, MySql ondersteund het mischien wel, maar dat is dan dus niet volgens de standaard.

[Adelbert]

Maar ookal doe ik het wel zo dan krijg ik nog steeds dezelfde melding

SQL:

SELECT *
FROM naw
WHERE blok LIKE '%0000"&j10&""&maand&"%' OR...... enz
ORDER BY SUBSTRING ( blok, 1 , 4 )

[bigbeng]

Geef de hele sql statement eens? Het lijkt erop dat er een syntax fout in je query zit. Of nog beter, laat het blok code eens zien waarmee je de sql samenstelt en de query vervolgens uitvoert.

[Woy]

bigbeng schreef op maandag 06 april 2009 @ 09:48:
Geef de hele sql statement eens? Het lijkt erop dat er een syntax fout in je query zit. Of nog beter, laat het blok code eens zien waarmee je de sql samenstelt en de query vervolgens uitvoert.

Nee laat de query eens zien nadat hij is samengesteld. Dus gewoon de query printen nadat hij is samen-gesteld.

Je laat nu telkens code zien waar nog een stukje ASP tussen staat.

[Adelbert]

De gehele code is nu dit:

SQL:

SELECT * FROM naw 
WHERE blok1 LIKE '%002908"&j10&""&maand&"%' 
OR blok1 LIKE '%002908"&j20&""&maand&"%' 
OR blok1 LIKE '%002908"&j30&""&maand&"%' 
OR blok1 LIKE '%002908"&j40&""&maand&"%' 
OR blok1 LIKE '%002908"&j50&""&maand&"%' 
OR blok1 LIKE '%002908"&j60&""&maand&"%' 
OR blok1 LIKE '%002908"&j70&""&maand&"%'
OR blok1 LIKE '%002908"&j80&""&maand&"%'
OR blok1 LIKE '%002908"&j90&""&maand&"%'
OR blok1 LIKE '%002908"&j100&""&maand&"%' 
OR blok2 LIKE '%002908"&j10&""&maand&"%' 
OR blok2 LIKE '%002908"&j20&""&maand&"%' 
OR blok2 LIKE '%002908"&j30&""&maand&"%' 
OR blok2 LIKE '%002908"&j40&""&maand&"%' 
OR blok2 LIKE '%002908"&j50&""&maand&"%' 
OR blok2 LIKE '%002908"&j60&""&maand&"%' 
OR blok2 LIKE '%002908"&j70&""&maand&"%'
OR blok2 LIKE '%002908"&j80&""&maand&"%'
OR blok2 LIKE '%002908"&j90&""&maand&"%'
OR blok2 LIKE '%002908"&j100&""&maand&"%' 
OR blok3 LIKE '%002908"&j10&""&maand&"%' 
OR blok3 LIKE '%002908"&j20&""&maand&"%' 
OR blok3 LIKE '%002908"&j30&""&maand&"%' 
OR blok3 LIKE '%002908"&j40&""&maand&"%' 
OR blok3 LIKE '%002908"&j50&""&maand&"%' 
OR blok3 LIKE '%002908"&j60&""&maand&"%' 
OR blok3 LIKE '%002908"&j70&""&maand&"%'
OR blok3 LIKE '%002908"&j80&""&maand&"%'
OR blok3 LIKE '%002908"&j90&""&maand&"%'
OR blok3 LIKE '%002908"&j100&""&maand&"%' 
ORDER BY SUBSTRING ( blok1, 1 , 4 )

Wat bedoel je met de samengestelde code

[bigbeng]

Dit is toch geen ASP code? Laat of het volledige blok code eens zien, of doe eens een Response.Write van de variabele die de resultaat SQL bevat. Nu staat er bijvoorbeeld dit:

code:

OR blok2 LIKE '%002908"&j90&""&maand&"%'

Als je dit naar een database server stuurt dan krijg je gegarandeerd fouten, dus ik neem aan dat dit een stukje uit je asp pagina is.

[Adelbert]

Hey gaat goed zolang ik het SUBSTRING gedeelte weg laat.

ASP schrijft zo weg:

SQL:

OR blok2 LIKE '%002908199904%'

[bigbeng]

Zit er wel een spatie of een enter in je resultaat SQL tussen de laatste OR statement en de ORDER BY?

[Adelbert]

Ja, ik kan ook wel een ORDER BY doen, en dat verloopt goed zolang ik maar niet iets met substring ga doen.

[bigbeng]

welke database server gebruik je? Ik heb even gezocht op AcuCorp ODBC en kwam daar COBOL tegen. Zit hier wel een echte database server achter?

[Vincenz0]

bigbeng schreef op maandag 06 april 2009 @ 12:03:
welke database server gebruik je? Ik heb even gezocht op AcuCorp ODBC en kwam daar COBOL tegen. Zit hier wel een echte database server achter?

AcuODBC is a communications interface designed to provide access to COBOL data files from Windows applications...

Based on Microsoft’s Open Database Connectivity (ODBC) standard, AcuODBC lets you retrieve and update ACUCOBOL-GT® indexed or relative data files from applications that have been ODBC-enabled. AcuODBC is literally an ODBC driver for Acucorp’s Vision file system. It translates SQL requests to COBOL I/O actions, and COBOL data to ODBC-compliant data sets.

Bron: http://www.acucorp.com/solutions/datasheets/acuodbc/

Hij maakt van SQL COBOL statements, je kan dus tegen limitaties lopen van die SQL implementatie, wat hier dus denk ik ook gebeurt met je substring.

@ TS, kijk een in de manual van AcuODBC wat zij implementeren voor de substring?

[ Voor 6% gewijzigd door Vincenz0 op 06-04-2009 12:18 ]

[Adelbert]

Het is idd een acuODBC koppeling.

[ Voor 28% gewijzigd door Adelbert op 06-04-2009 12:32 ]

[bigbeng]

KLIK

Of je pakt de handleiding erbij Je moet de SQL/92 syntax gebruiken bij functies, dus {fn <functienaam>(parameterlist)}.

Succes verder

[Woy]

bigbeng schreef op maandag 06 april 2009 @ 14:16:
KLIK

Of je pakt de handleiding erbij Je moet de SQL/92 syntax gebruiken bij functies, dus {fn <functienaam>(parameterlist)}.

Succes verder

Als ik die documentatie lees

SUBSTRING
{fn SUBSTR(`string')}
Locates a string within a string.

Doet SUBSTR heel wat anders dan dat het bij andere databases doet. Dus of het is een documentatie fout, of het is een hele vreemde versie van SUBSTR

[bigbeng]

Goed punt, had ik niet eens gezien. Ik zie het nut van substring zoals beschreven niet helemaal in, mogelijk is die inderdaad fout gedocumenteerd.

[Adelbert]

Bedankt heren, ik ga er morgen weer mee aan de slag.

Keep you posted.

[Adelbert]

SQL:

ORDER BY {fn SUBSTR (blok, 1 , 4)}

Is de code die ik nu heb en dit haalt nog steeds niets uit. Ik krijg wel een andere foutmelding nu:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: <identifier> expected, { found, near line 1, column 1166

default.asp, line 94

edit: omdat dit toch niet lekker werkt ga ik denk toch maar alles in een access db pompen en alles netjes indelen.

[ Voor 13% gewijzigd door Adelbert op 20-04-2009 10:09 ]

[Woy]

Probeer eerst eens een simpele query of het werkt

SQL:

SELECT {fn SUBSTR ( myTextField, 1, 4 )}
FROM myTestTable

En daarna een simpele test of het sorteren op het resultaat werkt

SQL:

SELECT myTextField
FROM myTestTable
ORDER BY {fn SUBSTR ( myTextField, 1, 4 )}

Als het daar al mis op gaat zou ik contact opnemen met AcuCorp, anders doe je zelf blijkbaar wat verkeerd.

De foumelding lijkt er trouwens op dat hij deze syntax gewoon niet ondersteund, hij loopt namenlijk te zeuren dat hij een { vind in plaats van een identifier.

Maar je data netter indelen is natuurlijk zowiezo aan te raden.

[ Voor 5% gewijzigd door Woy op 20-04-2009 10:21 ]

[Adelbert]

het lijkt er iid op:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[AcuCorp, Inc.][AcuODBC Driver]Scalar function not found: SUBSTR

[Woy]

Adelbert schreef op maandag 20 april 2009 @ 10:49:
het lijkt er iid op:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[AcuCorp, Inc.][AcuODBC Driver]Scalar function not found: SUBSTR

Dat is overigens wel een totaal andere melding. "De functie bestaat niet" vs "De syntax klopt niet"

[bigbeng]

Aangezien de documentatie waar ik naar refereerde al niet helemaal okee lijkt te zijn, stel ik voor dat je ook even SUBSTRING uitprobeert. Bij andere ODBC drivers wordt die nog wel eens gebruikt.