gregmelson
Posts: 19
|
| Posted: 03/31/2008, 8:46 AM |
|
Hi can anyone help, using Codecharge 4.0
and I wish to return a record (only 1) with the most up to date date.
SELECT *
FROM pfkunits INNER JOIN pfk777 ON
pfkunits.id = pfk777.unit_id
WHERE pfkunits.name = '{unit}' AND where pfk777.datetime = (SELECT max(datetime) FROM pfk777 where unit_id = '{unit}')
Please help. Any ideas welcome
_________________
greg
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 03/31/2008, 10:35 AM |
|
SELECT *
FROM pfkunits
INNER JOIN pfk777 ON pfkunits.id = pfk777.unit_id
WHERE pfkunits.name = '{unit}'
AND pfk777.datetime = (SELECT max(datetime) FROM pfk777 where unit_id = '{unit}')
remove the WHERE after AND
see if that does it for you
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/01/2008, 3:21 AM |
|
Thank you for responding wkempees,
I keep getting an error when I enter the SQL command is there something I'm doing wrong? 
I know I probably am doing some stupid, thank you for your advise. It really is appreciated :)
Best regards,
Greg Melson
_________________
greg
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/01/2008, 4:00 AM |
|
Oh well.....
First you take the hard way, by using the SQL version of VQB, where the TABLE version could have done it for you, that said.
Taken from you original post, the SQL shown here shows me something missing and something doubled.
upper right pane:
AND pfk777.datetime =
should show expr0
expr0 itself is missing the where unit_id
soI thik the subselect is generating more than 1 row
which will make the SQL fail as it expects exactly 1 value
So, get it to reflect my first respone post.
I would,
lose the expr0 in the SQL parameters
reconstruct the SQL to
SELECT *
FROM pfkunits
INNER JOIN pfk777 ON pfkunits.id = pfk777.unit_id
WHERE pfkunits.name = '{unit}'
AND pfk777.datetime = (SELECT max(datetime) FROM pfk777 where unit_id = '{unit}')
Hmmm, just noticed that {unit} is probably not the unit_id.
HTH
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
DonB
|
| Posted: 04/01/2008, 1:34 PM |
|
Always helps if you say what the error is, but here goes:
1. Expr0 is unneeded and unused. '{Expr0}' does not appear in the SQL pane.
2. You might need to alias pfk777 since it's used in the subquery AND in the
outer query.
3. If 'unit' is not a string, and the way it's used suggests it might be a
number, remove the apostrophes.
--
DonB
"gregmelson" <gregmelson@forum.codecharge> wrote in message
news:547f20c96ca993@news.codecharge.com...
> Thank you for responding wkempees,
>
> I keep getting an error when I enter the SQL command is there something
> I'm
> doing wrong?
> _________________
> greg
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/02/2008, 8:57 AM |
|
Awesome thanks DonB and wkempees it works now :) I removed the Expression and bang it worked :) AWESOME!
_________________
greg
|
|
|
|
|
