troubleshooting Question

ORA-00932 inconsistant data types - I have 2 queries in pass thro SQL one works and the other does not?

Avatar of PNickJames
PNickJamesFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessEnterprise SoftwareSQL
6 Comments1 Solution634 ViewsLast Modified:
I have 2 queries on a booking system for an id card with a photo(a blob) report. The first query here works fine. But I was asked to add the maximum expiry date from another table for each person. So I added the code I needed to do this. But now it seems I get an error with part of the code that was already working. Has anyone got any ideas of what I'm doing wrong here or if there's any syntax I might be missing?

Query one:
======================================================
select
   p.person_code,
   p.surname,
   p.forename,
 nvl(p.fes_user_6, 'n/a') BUS,
  CASE WHEN (to_date(sysdate) - to_date(p.date_of_birth)) /365.25 < 18 THEN to_char(p.date_of_birth,'dd/mm/yyyy') ELSE 'Over 18' END DOB,
(CASE WHEN b.id IS NOT NULL THEN
          (CASE WHEN (substr(webadminpkg.get_web_setting('PhotoSource'),-1,1)) = '\' THEN
             webadminpkg.get_web_setting('PhotoSource') || p.person_code || '.jpg'
           ELSE
             webadminpkg.get_web_setting('PhotoSource') || '\' || p.person_code || '.jpg'
           END)
         ELSE null END) photopath,
(CASE WHEN  b.id IS NOT NULL THEN
         b.binary_object
END) photodata,
(CASE WHEN  b.id IS NOT NULL THEN
         b.blob_type
END) phototype
from people p
LEFT OUTER JOIN blobs b     ON (b.owner_ref    = p.person_code AND b.domain = 'PEOPLE')
where
  p.person_code = 17867

Second Query:
=======================================================
select distinct
   p.person_code,
   p.surname,
   p.forename,
 nvl(p.fes_user_6, 'n/a') BUS,
to_char(max(r.fes_spcl_end_date)) expdate,
  CASE WHEN (to_date(sysdate) - to_date(p.date_of_birth)) /365.25 < 18 THEN to_char(p.date_of_birth,'dd/mm/yyyy') ELSE 'Over 18' END DOB,
(CASE WHEN b.id IS NOT NULL THEN
          (CASE WHEN (substr(webadminpkg.get_web_setting('PhotoSource'),-1,1)) = '\' THEN
             webadminpkg.get_web_setting('PhotoSource') || p.person_code || '.jpg'
           ELSE
             webadminpkg.get_web_setting('PhotoSource') || '\' || p.person_code || '.jpg'
           END)
         ELSE null END) photopath,
(CASE WHEN  b.id IS NOT NULL THEN
         b.binary_object
END) photodata,
(CASE WHEN  b.id IS NOT NULL THEN
         b.blob_type
END) phototype
from people p
LEFT OUTER JOIN blobs b     ON (b.owner_ref    = p.person_code AND b.domain = 'PEOPLE')
LEFT OUTER JOIN registration_units r  ON (r.fes_per_person_code= p.person_code)
where
  p.person_code = 17867
GROUP BY p.person_code
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros