Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
16
Erster Beitrag:
vor 9 Monaten, 3 Wochen
Letzter Beitrag:
vor 9 Monaten, 2 Wochen
Beteiligte Autoren:
Joel, Stefan Bentvelsen, David Egan, Chris cordes, Al, kingdr, Peter Holemans, DerekT

SQL Order By error - syntax must be wrong

Startbeitrag von Joel am 29.12.2016 20:39

Hi everyone -

Working on a simple SQL Query in WebDev but can't seem to get the 'Order By' part to work - works without that line just fine - any ideas?

Antworten:

Hey Joel,

Without the SQL posted it will be hard to tell you anything useful I'm afraid.

Cheers,

Peter

von Peter Holemans - am 30.12.2016 09:34
Whoops! Sorry about that! Here it is...

sSQL is string = [
SELECT *
FROM gpsdContact
WHERE
bActiveMembershipFlag = 1
AND
bDeleted = 0
AND
SUBSTRING (gpsdContact.dDOB, 5,2) = '%1'
]

von Joel - am 30.12.2016 15:24
Joel

I do not see an ORDER BY statement.

Can you post ALL of your code used for running this query.

von DerekT - am 30.12.2016 16:49
Hi

by using your example, you should assign

sSQL is string = [
MAKE no SPACE of [ then next line
SELECT *
^ MAKE 1 SPACE after *
FROM gpsdContact
^ so does after gpsdContact
ORDER BY ...
]

There's something related to the assignment of string within [ ... ] issue...

HTH

King

von kingdr - am 30.12.2016 17:55
Perhaps 3rd time's a charm... I want to sort by the day of the month....

sSQL is string = [
SELECT *
FROM gpsdContact
WHERE
bActiveMembershipFlag = 1
AND
bDeleted = 0
AND
SUBSTRING (gpsdContact.dDOB, 5,2) = '%1'
ORDER BY (SUBSTRING (gpsdContact.dDOB, 5,2))
]

von Joel - am 31.12.2016 17:24
Hi Joel,

I think you have to use something like:

sSQL is string = [
SELECT *, SUBSTRING (gpsdContact.dDOB, 5,2) AS Month
FROM gpsdContact
WHERE
bActiveMembershipFlag = 1
AND
bDeleted = 0
AND
SUBSTRING (gpsdContact.dDOB, 5,2) = '%1'
ORDER BY Month ASC

But why do you want to sort on the month if you only got records of one month?

von Stefan Bentvelsen - am 31.12.2016 18:19
I lied - just can't seem to get this thread right... lol I want to sort by DAY of the month. Thanks! I'll try this right now. Didn't know about doing it this way.

von Joel - am 31.12.2016 18:20
Ok, that worked - sort of - it's sorting it as text, not as a number - any way to fix that?

von Joel - am 31.12.2016 18:28
Hi Joel,

may be you can CAST() it to an integer?

von Stefan Bentvelsen - am 31.12.2016 22:46
Hello Joel

Why not use the query wizard ?

Regards
Al

von Al - am 31.12.2016 22:52
Hi there -

Some of my projects don't use a Analysis - it's in the main project only and then I create Data sources in my other projects to link to the data files, so can't use the Query Editor most of the time.

I can't figure out how to get CAST() into the SQL Query and have it work...

von Joel - am 02.01.2017 17:56
Hi Joel
Testing it here the sort was in correct order which I would have expected seeing as you're explicitly extracting 2 digits from the string, unless your DOB field is not structured as a HFSQL date. .

However as that's not working for you try something like

sSQL is string = [
SELECT *, CAST(SUBSTRING (gpsdContact.dDOB, 5,2) AS INTEGER) AS Month
FROM gpsdContact
WHERE
bActiveMembershipFlag = 1
AND
bDeleted = 0
AND
SUBSTRING (gpsdContact.dDOB, 5,2) = '%1'
ORDER BY Month ASC
]


David

von David Egan - am 03.01.2017 06:56
Hi Gang -

I'm sorry, but it did work without the Cast() statement. Thanks for your help everyone!

J

von Joel - am 03.01.2017 18:03
PMFJI

If you want to sort by day, and you've already filtered by month, just order the result by the date. It will come out in day order rather than text.

sSQL is string = [
SELECT *
FROM gpsdContact
WHERE
bActiveMembershipFlag = 1
AND
bDeleted = 0
AND
SUBSTRING (gpsdContact.dDOB, 5,2) = '%1'
ORDER BY gpsdContact.dDOB
]

Though, you'd be faster if you used between rather than the filtering on the subscript
...
AND
gpsdContact.dDOB BETWEEN '%1' and '%2'
ORDER BY gpsdContact.dDOB
]

Given the month, your buildstring would have FirstDayofMonth(Year,month) and LastDayofMonth(year,month)
This query allows for more flexibility in ranges as well.

HTH,
Chris C

von Chris cordes - am 03.01.2017 18:53
Thank you Chris!

J

von Joel - am 05.01.2017 17:56
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.