Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 11 Monaten, 2 Wochen
Letzter Beitrag:
vor 11 Monaten, 2 Wochen
Beteiligte Autoren:
joe crevino, Fabrice Harari, Steven Sitas, Curtis

Problem with query

Startbeitrag von joe crevino am 02.09.2016 19:07

Have a query that runs perfect in msql management studio. Runs perfect thru the query editor in windev. But... a error show up when the project is compiled. Hence I can not get to the query to hook it into a table. Any Ideas.

Antworten:

Need more info. What's the error? Do you have example sql for us to look over? Also, which PCSoft product and version are you using?

von Curtis - am 02.09.2016 19:21
Hi

on top of previous questions, what code do you use to execute it?

Best regards

von Fabrice Harari - am 02.09.2016 20:17
SELECT
SUM(BOOKED_OPTION.BOOKEDOPTIONTOTALSELLINGAMOUNT) AS tsell,
BOOKING.BOOKINGID,
BOOKING.BOOKINGREFERENCENUMBER,
BOOKING.CLIENTID,
CLIENT.CLIENTNAME,
ADDRESS.ADDRESSLINE4,
BOOKING.BOOKINGSTARTDATE,
CLIENT.CLIENTID,
BOOKING.BOOKINGREFERENCENUMBER,
BOOKING.BOOKINGCONFIRMATIONDATE,
LOGIN_ACCOUNT.LOGINACCOUNTNAME,
BOOKING.BOOKINGCLIENTCONTACT,
BOOKING_STATUS.BOOKINGSTATUSNAME,
BOOKING.BOOKINGNAME,
STATE.STATENAME


FROM
BOOKING,
BOOKED_SERVICE,
BOOKED_OPTION,
CLIENT,
ADDRESS,
LOGIN_ACCOUNT,
BOOKING_STATUS,
STATE

WHERE
BOOKING.CLIENTID = 6058
AND BOOKED_SERVICE.BOOKEDSERVICEID = BOOKED_OPTION.BOOKEDSERVICEID
AND BOOKING.BOOKINGID = BOOKED_SERVICE.BOOKINGID
AND BOOKING.BOOKINGSTARTDATE BETWEEN '20160101' AND '20161231'
AND CLIENT.CLIENTID = BOOKING.CLIENTID
AND ADDRESS.CLIENTID = CLIENT.CLIENTID
AND LOGIN_ACCOUNT.LOGINACCOUNTID = OOKING.CREATEDBY_LOGINACCOUNTID
AND BOOKING_STATUS.BOOKINGSTATUSID = BOOKING.BOOKINGSTATUSID
AND STATE.STATEID = ADDRESS.STATEID

GROUP BY

BOOKING.BOOKINGID,
BOOKING.BOOKINGREFERENCENUMBER,
BOOKING.CLIENTID,
CLIENT.CLIENTNAME,
ADDRESS.ADDRESSLINE4,
BOOKING.BOOKINGSTARTDATE,
CLIENT.CLIENTID,
BOOKING.BOOKINGREFERENCENUMBER,
BOOKING.BOOKINGCONFIRMATIONDATE,
LOGIN_ACCOUNT.LOGINACCOUNTNAME,
BOOKING.BOOKINGCLIENTCONTACT,
BOOKING_STATUS.BOOKINGSTATUSNAME,
BOOKING.BOOKINGNAME,
STATE.STATENAME


this is the error code
Warning : The use of aggregate functions with other items or non-aggregate functions is not allowed without using a GROUP BY clause. All the items or non-aggregate functions must be found in the GROUP BY clause.
qwagents_by_year_direct_grand_totals_by_agent, SQL Code, line 1, column 1

von joe crevino - am 02.09.2016 20:33
using windev 21

native access

von joe crevino - am 02.09.2016 20:38
and again, your wlanguage code is?

von Fabrice Harari - am 03.09.2016 13:08
Hi Joe,
If I understand your question correctly, take a look here:
http://stackoverflow.com/questions/5920070/why-cant-you-mix-aggregate-values-and-non-aggregate-values-in-a-single-select

What bothers me is that you say it works in Management Studio and the query editor in WinDEV (?). So maybe I got it wrong ....

But you can use "HExecuteSQLQuery" in "hQueryWithoutCorrection MODE".
http://doc.windev.com/en-US/index.awp?3044084

-----------------------------------------------------------------------
When doing a query with GROUP BY, it is generally a good idea -with WX- to SPLIT your query, to 2 or more queries.

One query for the SELECT
and a second one on the FIRST query (Group etc.)

This way you bypass any syntax problems _AND_ it is a LOT faster, specially with HFSQL C/S .....
-------------------------------------------------------------------------

Steven Sitas
www.alpha360.biz

von Steven Sitas - am 03.09.2016 14:29
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.