Recent

Author Topic: Problem with SQL - query  (Read 22641 times)

timmermanj

  • New Member
  • *
  • Posts: 39
Problem with SQL - query
« on: July 05, 2010, 07:29:37 pm »
I'm a bit unexperienced with SQL.
I understand the basics but i have a problem to make following query (see attachment).

I hope someone can help me started with this.
I would really appreciate.

Thanks a lot!!!
Jeffrey

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Problem with SQL - query
« Reply #1 on: July 05, 2010, 10:29:44 pm »
I'm a bit unexperienced with SQL.
I understand the basics but i have a problem to make following query (see attachment).

I hope someone can help me started with this.
I would really appreciate.

Thanks a lot!!!
Jeffrey
1. What db? (unfortunately there is no such thing as standard SQL.)
2. What have you tried so far?
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

timmermanj

  • New Member
  • *
  • Posts: 39
Re: Problem with SQL - query
« Reply #2 on: July 06, 2010, 12:37:57 pm »
Sorry, the database I use for my application is MS Access.

I've tried something like this.

SELECT SerialNumber AS SN,

           (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN   
            #07/01/2010# AND #07/05/2010# AND SerialNumber = SN
            AND Department = "Shear") AS ShearTotal,

           (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN   
            #07/01/2010# AND #07/05/2010# AND SerialNumber = SN
            AND Department = "Punch") AS PunchTotal,

           (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN   
            #07/01/2010# AND #07/05/2010# AND SerialNumber = SN
            AND Department = "Break") AS BreakTotal,

           (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN   
            #07/01/2010# AND #07/05/2010# AND SerialNumber = SN
            AND Department = "AS") AS ASTotal

 FROM FollowUpTable WHERE FUDate BETWEEN #07/01/2010# AND #07/05/2010#


as you can  see i'am using subqueries & it makes this very long difficult to read.
I hope everything is clear.

Thanks a lot!

LazaruX

  • Hero Member
  • *****
  • Posts: 597
  • Lazarus original cheetah.The cheetah doesn't cheat
Re: Problem with SQL - query
« Reply #3 on: July 06, 2010, 02:09:30 pm »
I am not very good in SQL too, but if you are using MSAccess I would suggest you to do the queries inside Access itself and then just select from those Query tables. This is much faster than doing a query yourself, because in this way the query will be seen as a table.

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Problem with SQL - query
« Reply #4 on: July 06, 2010, 04:03:00 pm »
Not really a Lazarus question but a pure SQL question.

You cannot easily do reporting with SQL (i.e. building tables with nice headers and transposed results). You'll need some sort of reporting component for that. I haven't look at it but maybe this topic gives you something useful.

Now for the query: you can use a GROUP BY clause on the SerialNumber and Department columns. That will give you the correct results in a simple table:

serialnumberdepartmentsum
11111shear8
11111Punch2
11111Break0
11111AS0
22222shear4
etc...

If you're lucky ACCESS supports the latest PIVOT function that is available in SQL Server 2005, so you can get the requested result.
If not then you'll need either a reporting component as mentioned above or stick to the query you already built yourself.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

JuhaManninen

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4459
  • I like bugs.
Re: Problem with SQL - query
« Reply #5 on: July 06, 2010, 07:46:27 pm »
I am not very good in SQL too, but if you are using MSAccess I would suggest you to do the queries inside Access itself and then just select from those Query tables. This is much faster than doing a query yourself, because in this way the query will be seen as a table.

In my experience it is not really faster. I haven't tried the latest versions of MS Access though.
You can read the Query table like it was a normal table but Access does execute the query always when you read it.
I tried to use it for a design tool which connects to a DB table for components.
It felt like a good idea to let an Access query connect 2 tables together on the fly but it was so slow I finally had to make a new joined table.
The speed difference was maybe *20.

Juha
Mostly Lazarus trunk and FPC 3.2 on Manjaro Linux 64-bit.

timmermanj

  • New Member
  • *
  • Posts: 39
Re: Problem with SQL - query
« Reply #6 on: July 08, 2010, 03:22:18 pm »
I've made the SQL - query with the Wizard Query in MS Access.

I've got this SQL statement as result:

TRANSFORM Sum(FollowUpTable.[FinishedHours]) AS SomVanFinishedHours
SELECT FollowUpTable.[SerialNumber], Sum(FollowUpTable.[FinishedHours]) AS [Totaal FinishedHours]
FROM FollowUpTable
GROUP BY FollowUpTable.[SerialNumber]
PIVOT FollowUpTable.[Department]


When i try this query in Lazarus i got following error:

"Cannot open a non-select statement."

So I tried the same SQL query but now i didn't try to open it, instead i tried executing it (ExecSQL).

I've got the SQL statement from the wizard in access & in Access itself I get the result i want.

However when i try to open or execute it in an application in Lazarus I'am getting these errors.

Anybody any idea what's wrong?

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Problem with SQL - query
« Reply #7 on: July 08, 2010, 05:54:31 pm »
This seems like a Lazarus parser error, that is incompatible with the MS Access SQL Dialect.
I cannot test it myself but have you tried setting ParseSQL to false?
(Assuming you are using a TSQLQuery...)
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

timmermanj

  • New Member
  • *
  • Posts: 39
Re: Problem with SQL - query
« Reply #8 on: July 09, 2010, 08:34:16 am »
I'm using the TSQLQuery component.

I've set ParseSQL to false but still no success...

To do such a Transform - Pivot SQL query with the TSQLQuery do i have to use
"Open" or "ExecSQL" to open the query?

I've tried them both & with both no luck.

Could you please try to open a simple "TRANSFORM - PIVOT" query in a lazarus app?
Maybe you can find the error easily this way.

Thanks in advance!

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Problem with SQL - query
« Reply #9 on: July 09, 2010, 12:55:22 pm »
It's a bit difficult testing when I don't have your setup.
I don't mind looking at the problem, but then you'd have to upload a minimal version of your software that shows the error.

All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

timmermanj

  • New Member
  • *
  • Posts: 39
Re: Problem with SQL - query
« Reply #10 on: July 09, 2010, 03:14:40 pm »
Ok! I'll sent a simplified version when it's ready!

Thanks a lot!

timmermanj

  • New Member
  • *
  • Posts: 39
Re: Problem with SQL - query
« Reply #11 on: July 11, 2010, 01:34:42 pm »
I've finished a mini application with the table and query i've tried.

If you start the app. you can see the table in it's normal condition.
The box beneath the table contains the SQL - query i've tried to pivot the table.
I've got this SQL - statement from the wizard in Access 2003.

PS. if you open the query tab in the access database, you can see the cross-table query that the wizard has generated.

If you want to, you can give me your e-mail or tell me where to upload & i'll send you a rar with these files.

Thanks in advance!
« Last Edit: July 11, 2010, 01:37:09 pm by timmermanj »

Lacak2

  • Guest
Re: Problem with SQL - query
« Reply #12 on: July 12, 2010, 07:59:27 am »
I've made the SQL - query with the Wizard Query in MS Access.

I've got this SQL statement as result:

TRANSFORM Sum(FollowUpTable.[FinishedHours]) AS SomVanFinishedHours
SELECT FollowUpTable.[SerialNumber], Sum(FollowUpTable.[FinishedHours]) AS [Totaal FinishedHours]
FROM FollowUpTable
GROUP BY FollowUpTable.[SerialNumber]
PIVOT FollowUpTable.[Department]


When i try this query in Lazarus i got following error:

"Cannot open a non-select statement."

So I tried the same SQL query but now i didn't try to open it, instead i tried executing it (ExecSQL).

I've got the SQL statement from the wizard in access & in Access itself I get the result i want.

However when i try to open or execute it in an application in Lazarus I'am getting these errors.

Anybody any idea what's wrong?
IMHO you do nothing wrong.
Simply there is SQLParser, which do not understand token TRANSFORM + SELECT as valid select statement.
You should report this as bug in bug tracker.

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Problem with SQL - query
« Reply #13 on: July 12, 2010, 10:11:57 am »
Simply there is SQLParser, which do not understand token TRANSFORM + SELECT as valid select statement.
You should report this as bug in bug tracker.

The query given is not a valid select statement, it's some sort of M$ specific TRANSFORM thingy.
The way Lazarus should handle this is execute the query without interpretation, and if it gets a valid resultset back, process it accordingly.
The bug, if there is one, is somehow in the preprocessing that's done before the query is executed. And there should be a way to skip that.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

Lacak2

  • Guest
Re: Problem with SQL - query
« Reply #14 on: July 12, 2010, 02:21:13 pm »
The query given is not a valid select statement, it's some sort of M$ specific TRANSFORM thingy.
It is not valid in POV of SQL standard spec., but from MS POV it is kind of select.

The bug, if there is one, is somehow in the preprocessing that's done before the query is executed.
Yes, exactly. Before Prepare is invoked SQLParser, which returns StatementType. if it does not recognize as "select" or "exec procedure" and you "Open" then exception is raised.

And there should be a way to skip that.
But IMHO there is ATM no such way

 

TinyPortal © 2005-2018