SQL Query help

[savok]

here is my SQL Query which works fine. But to it I need to add records in which applic.`appl_stat` = 'SU0' and applic.`casemangr` = '000'

//original query
SELECT
applic.`idnumber`,
applic.`appl_stat`,
applic.`casemangr`,
refer.`idnumber`,
refer.`proj_from`,
refer.`answer`,
refer.proj_from + refer.answer
FROM
`applic` applic LEFT OUTER JOIN `refer` refer ON applic.`idnumber` = refer.`idnumber`
WHERE
applic.`appl_stat` = 'EN'
and
refer.`idnumber` NOT IN (SELECT refer.`idnumber` FROM `refer` refer WHERE refer.`proj_from` = '018601' AND refer.`answer` = 'T')
ORDER BY
applic.`idnumber` ASC


//this is the new query which crashed crystal when i run it
SELECT
applic.`idnumber`,
applic.`appl_stat`,
applic.`casemangr`,
refer.`idnumber`,
refer.`proj_from`,
refer.`answer`,
applic.appl_stat + applic.casemangr
FROM
`applic` applic LEFT OUTER JOIN `refer` refer ON applic.`idnumber` = refer.`idnumber`
WHERE
applic.`appl_stat` = 'EN'
and
refer.`idnumber` NOT IN (SELECT refer.`idnumber` FROM `refer` refer WHERE refer.`proj_from` = '018601' AND refer.`answer` = 'T')
or
applic.`appl_stat` = 'SU0' and applic.`casemangr` = '000'
ORDER BY
applic.`idnumber` ASC


I also tried making an expression for

or
applic.appl_stat + applic.casemangr = 'SU0000' but that gave an error of Invalid Key Length?


any suggestions?

thanks

 

[jjames]

what do you mean 'crashed crystal' ?

Does it lockup? Give you an error? Not respond? What?

 

[savok]

oops made a mistake it should be

WHERE
applic.`appl_stat` = 'SU0' AND
applic.`casemangr` <> '000'


James it just gave an error saying Crystal performed an illegal operation. But I think it might of been because I made a mistake of using = instead of <>.


I tried this one, but it gives an error saying invalid Key Length

/////
SELECT
applic.`idnumber`,
applic.`appl_stat`,
applic.`casemangr`,
refer.`idnumber`,
refer.`proj_from`,
refer.`answer`,
refer.proj_from + refer.answer
FROM
`applic` applic LEFT OUTER JOIN `refer` refer ON applic.`idnumber` = refer.`idnumber`
WHERE
applic.`appl_stat` = 'EN'
and
refer.`idnumber` NOT IN (SELECT refer.`idnumber` FROM `refer` refer WHERE refer.`proj_from` = '018601' AND refer.`answer` = 'T')
or
applic.`appl_stat` = 'SU0' AND applic.`casemangr` <> '000'
ORDER BY
applic.`idnumber` ASC
/////////

 

[savok]

maybe this will help. These are the two working queries. I need to combine them into one.


SELECT
applic.`idnumber`,
applic.`appl_stat`,
applic.`casemangr`,
refer.`idnumber`,
refer.`proj_from`,
refer.`answer`,
refer.proj_from + refer.answer
FROM
`applic` applic LEFT OUTER JOIN `refer` refer ON applic.`idnumber` = refer.`idnumber`
WHERE
applic.`appl_stat` = 'EN'
and
refer.`idnumber` NOT IN (SELECT refer.`idnumber` FROM `refer` refer WHERE refer.`proj_from` = '018601' AND refer.`answer` = 'T')
ORDER BY
applic.`idnumber` ASC

SELECT
applic.`idnumber`,
applic.`appl_stat`,
applic.`casemangr`
FROM
`applic` applic
WHERE
applic.`appl_stat` = 'SU0' AND
applic.`casemangr` <> '000'
ORDER BY
applic.`idnumber` ASC

 

[jjames]

One thing that looks funny is the WHERE clause, You need some brackets I think like below...

.
.
.
WHERE
(
applic.`appl_stat` = 'EN'
and
refer.`idnumber` NOT IN (SELECT refer.`idnumber` FROM `refer` refer WHERE refer.`proj_from` = '018601' AND refer.`answer` = 'T')
)
or
( applic.`appl_stat` = 'SU0' AND applic.`casemangr` <> '000'
)
ORDER BY
applic.`idnumber` ASC

 

[savok]

Hmm i thought that was it too, but it still gives the same Invalid Key Length error

 

[savok]

The full error msg

ODBC error: [Microsoft][ODBC Visual FoxPro Driver] Invalid key length.

 

[thomasyu]

Do you have any null condition that you have not been catered?

 

[savok]

No idea about nulls, but i dont think so?

If i cant get this query to work as one, is there any way i can make a crystal report on 2 queries?

 

[IdoMillet]

savok,

You are using the same aliases for the tables
in the outer query and the tables in the subquery.

Change the alias names for the tables in the subquery
to other names.

Cheers,

- Ido ixm7@psu.edu