Max date for record Set in "Add Command" function 2

[TakeCover328]

Ok, here is what I am trying to do. I usually use access to create subqueries that led to a master query. Each of these sub queries has information that leads to data that is used to create statistics for reviews. What I am trying to do is replicate this process in Crystal Reports. The first step that I am taking is create a sql statement in the "Add Command" function in Crystal that will pull the maximum date for each grouped set of information. I want this to be done prior to its placement in the database because this is only one of many sub-queries. So...

:::TABLE SAMPLE:::
Table Name: ES-SR:TaskAuditHistory
task-id;: status; : Group; : Individual; : Create-Date
---------------------------------------------------------
12345 : Assigned : AccessCtrl: Null : 4/1/2008
12345 : Completed: SvcDsk : Bob : 4/2/2008
12345 : Assigned : SiteSuppor: Jack : 3/3/2008
54321 : Assigned : AccessCtrl: Joe : 4/1/2008
54321 : Assigned : AccessCtrl: Jim : 4/1/2008
---------------------------------------------------------

Ok, so what I want is to see the Max Date for each Task ID assigned to Access Control.

Here is what I have for the command:

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> ''
GROUP BY
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date

...

I have attempted to add in to the WHERE clause:

Create-Date IN
(SELECT
MAX(Create-Date)
FROM ES-SR:TaskAuditHistory
GROUP BY Task-ID, Status, Assigned-Group)

That didnt work so then I tried:

Create-Date IN (Select Max(Create-Date) FROM ES-SR:TaskAuditHistory as F where F.Create-Date = ES-SR:TaskAuditHistory.Create-Date GROUP BY Task-ID, Status, Assigned-Group)

Neither of them worked, I get the follow error:

Failed to open a rowset. Details: 420river]Expectied lexical element not found: <Identifier>

I am using Crystal Reports 10; I am connecting through an AR System ODBC Source.

To me, it seems as if the ad hoc query is not running so it has nothing to compare the create date to? Thoughts?

 

[kskid]

Try this

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> '' and
create_date = (select max(TAH.create_date
from ES-SR:TaskAuditHistory TAH
where TAH.status = 'Assigned'
and TAH.assigned-group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Task-ID = Task-id)
GROUP BY
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date


-lw

 

[TakeCover328]

Still getting that error, could it be because I am connecting through ODBC?

 

[TakeCover328]

Actually, I take that back... I changed a few of the _ to - in the command you posted, and now the error has changed;

Failed to open a rowset. Details: 420river]Unexpected extra token: Create-Date

 

[lbass]

If you are only using some groups for the subquery, then I don't think you should be using a group by clause in the command. Instead try kskid's solution without the group by clause (and adding the missing paren and with a minor change in the subquery):

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> '' and
create_date = (select max(TAH.create_date)
from ES-SR:TaskAuditHistory TAH
where TAH.status = 'Assigned'
and TAH.assigned-group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Task-ID = ES-SR:TaskAuditHistory.Task-id)

-LB

 

[TakeCover328]

Darn, Tried that as well; No luck

... Failed to open a rowset. Details: 420river]Expectied lexical element not found: <Identifier>

Its acting as if that subquery statement isnt existing. I put the subquery by it self and it ran fine; but when ever I put it into this command, I get the error.

This is exactly what I have in the command...

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> '' and
Create-Date = (select max(TAH.create-date)
from ES-SR:TaskAuditHistory TAH
where TAH.Status = 'Assigned'
and TAH.Assigned-Group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Task-ID = ES-SR:TaskAuditHistory.Task-id)

 

[lbass]

Where did you run the subquery by itself? In a command? As a SQL expression?

-LB

 

[TakeCover328]

Yes, I put the sub query in a seperate command. It was accepted and allowed it to save, rather then erroring for debug.

 

[lbass]

Does this work on its own?

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> ''

-LB

 

[kskid]

What kid of database are we accessing. The above formula will work in PL/SQL against an Oracle database.... In this particular case, sort of.

Are the column names you are using the actual names? If so, Oracle will only allow alphanumeric characters along with #, $ and _. The hypen (-) or dash you are using would not be a valid character may be treating that as a subtraction by the SQL you are using.

 

[TakeCover328]

lbass...

Yes, it works on its own, I am unsure why it doesnt work as a subquery?

kskid...

It is an AR Database. I have it set to remove special characters but I dont think it removes hyphens. Wouldnt quoting a field override the subtraction function and treat it as a field?

 

[kskid]

I know the subquery works with PL/SQL for Oracle. Not familiar at all with the SQL that is used with your AR Database.

I apologize for not being able to give you much help.

Good Luck.

-lw

 

[lbass]

I was not asking you about the subquery. Also, I am assuming you are trying this in the add command area of CR, not in some other application.

I too was wondering about the punctuation for the table/field names--that's why I asked you to run the query without the subquery to see if it worked.

-LB

 

[TakeCover328]

Oh, Ok. Yeah it is in the "Add Command" area of CR.

This works on its own;

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> ''

As does;

select max(TAH.create-date)
from ES-SR:TaskAuditHistory TAH
where TAH.Status = 'Assigned'
and TAH.Assigned-Group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Task-ID = ES-SR:TaskAuditHistory.Task-id

I have set the ODBC driver to remove and replace unacceptable formating, and replace it with _ . Any ideas of how I can convert them in another way?

kskid...

I do thank you for your help, It has definalty been educational. Thank you!

 

[kskid]

Just humor me and try this. There may be some problem in identifying which TaskAuditHistory table is being used.

SELECT
TA.Task-ID,
TA.Status,
TA.Assigned-Group,
TA.Assigned-Individual,
TA.Create-Date
FROM
ES-SR:TaskAuditHistory TA
WHERE
TA.Status = 'Assigned' and
TA.Assigned-Group = 'Access Control' and
TA.Assigned-Individual <> '' and
TA.Assigned-Individual is not null and
TA.Create-Date = (select max(TAH.create-date)
from ES-SR:TaskAuditHistory TAH
where TAH.Status = 'Assigned'
and TAH.Assigned-Group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Assigned-Individual is not null
and TAH.Task-ID = TA.Task-id)

-lw

 

[TakeCover328]

Still no luck... I have made a new connection, changing all -'s to _. Here is what i am using now...

SELECT
TA.Task_ID,
TA.Status,
TA.Assigned_Group,
TA.Assigned_Individual,
TA.Create_Date
FROM
ES_SR:TaskAuditHistory TA
WHERE
TA.Status = 'Assigned' and
TA.Assigned_Group = 'Access Control' and
TA.Assigned_Individual <> '' and
TA.Assigned_Individual is not null and
TA.Create_Date = (select max(TAH.create_date)
from ES_SR:TaskAuditHistory TAH
where TAH.Status = 'Assigned'
and TAH.Assigned_Group = 'Access Control'
and TAH.Assigned_Individual <> ''
and TAH.Assigned_Individual is not null
and TAH.Task_ID = TA.Task_id)

 

[kskid]

Hmmmm....

In oracle, the database name that preceeds the tablename is separated by a period. I noticed that yours has a colon if that means anything.

-lw

 

[TakeCover328]

Thanks. The : is part of the naming system :/

 

[TakeCover328]

Would you guys have a better recommendation for a system that I can use to generate this information? I am trying to provide my organzation with automated data reporting systems that require more complex reporting processes then they are used to. I have been using MS Access for years but I want to create a stand alone eventually.

 

[kskid]

I'll defer to the more experienced lbass. I can only speak about Oracle and PL/SQL since 1995 and added Crystal Reports in 2001. I do know Oracle is expensive so that may not be a cost effective option for you.

I'm thinking MS SQL Server would be more to your benefit.

Good Luck,
-lw