Trying to use SQL subquery statement in Crystal 10 4

[glthornton]

Hi everyone,
I'm trying to use an SQL Expression field to determine the number of ApptID's where it finds an Action code of 15 and an Action code of 0 on the same day. Example of the data: (tblAltApptHistory)
ApptID Date ActionID
123 10/01/2005 15
123 10/01/2005 0
124 10/01/2005 0
125 10/02/2005 0
125 10/02/2005 15

This is what I have for a query right now:
SELECT DISTINCT `tblAltApptHistory`.`AppID`
FROM `tblAltApptHistory`
WHERE `tblAltApptHistory`.`Action`= 15 AND `tblAltApptHistory`.`AppID`=
(SELECT DISTINCT `tblAltApptHistory`.`AppID` FROM `tblAltApptHistory` WHERE `tblAltApptHistory`.`Action`= 0)

But when I try to check the query, I get a "DAO Error Code: 0xd1a Description at most one record can be returned by the subquery"

If this is not the correct approach to take to get my expected results, does anyone have any recommendations?

What I'd like to get back from the query is:
ApptID
123
125

Thank you!!

 

[synapsevampire]

I guess that you mean SQL Server when you say a SQL Subquery.

The error is probably right, try changing the syntax to:

SELECT top 1 DISTINCT `tblAltApptHistory`.`AppID`
FROM `tblAltApptHistory`
WHERE `tblAltApptHistory`.`Action`= 15 AND `tblAltApptHistory`.`AppID`=
(SELECT TOP 1`tblAltApptHistory`.`AppID` FROM `tblAltApptHistory` WHERE `tblAltApptHistory`.`Action`= 0)

Not sure if this is right without seeing the database structures, but it should eliminate the error.

I think that yoiu probably want something that is related to the original data though, which whis query doesn't seem to be doing.

-k

 

[Turkbear]

Hi,
Why not just:

SELECT DISTINCT `tblAltApptHistory`.`AppID`
FROM `tblAltApptHistory`
WHERE (`tblAltApptHistory`.`Action`= 15 AND `tblAltApptHistory`.`Action`= 0)

since your code does not use the Date in any way..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Turk, the sql you reference would state that the same value would exist in every row, which is impossible.

If they used an OR then they would get both.

-k

 

[Turkbear]

Hi,
OOPS, read it too fast...but,OR would get both but not just those with the same date, so it seems that the subquery must take into account the date to satisfy the requirements.


Not good enough with SqlServer syntax to provide an example, however..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I agree, Turk, the entire SQL seemed odd to me, I just cleaned up the syntax error.

I don't have time to guess at requirements, and people are reluctant if not angered by requests for reasonable technical information in posts.

-k

 

[lbass]

First, I think you need to use "add command" rather than a SQL expression for this. Create two separate commands:

[command1]:
SELECT DISTINCT `tblAltApptHistory`.`AppID`,`tblAltApptHistory`.`Date`,`tblAltApptHistory`.`Action`
FROM `tblAltApptHistory`
WHERE `tblAltApptHistory`.`Action`= 15

[command2]:
SELECT DISTINCT `tblAltApptHistory`.`AppID`,`tblAltApptHistory`.`Date`,`tblAltApptHistory`.`Action`
FROM `tblAltApptHistory`
WHERE `tblAltApptHistory`.`Action`= 0

Link the commands on AppID and on Date (change the `Date` to match your datefield), but not on action. Right click on the link->link options and choose "Enforce both".

This should return only the records you wish to see.

-LB

 

[-cush-]

ApptID's where it finds an Action code of 15 and an Action code of 0 on the same day:

SELECT ApptID,[Date],Max(ActionID),Min(ActionID)
FROM tblAltApptHistory
WHERE ActionID <= 15
group by ApptID,[Date]
having Max(ActionID) = 15 and Min(ActionID) = 0

 

[glthornton]

Thanks Everyone!! It's working now.