problem with multiple EXISTS

[ScanX]

Hello,

I have written this query, unfortunately Sybase doesnt allow me to execute it (a maximum of 1 subquery predicate per conjunct is supported in this release...which is the latest one)

Maybe it's solvable by using UNION and repeating the query for each exists, but then I will have a problem with the count as I will receive 6 counts for the same DBA.dim_action.action_group_descr

Anyone has an idea how to solve this ?

The query :
-----------

SELECT
DBA.dim_action.action_group_descr,
count (distinct R.ind_key)
FROM
DBA.dim_individual,
DBA.fac_ind_reaction R,
DBA.dim_action,
DBA.dim_email

WHERE
DBA.dim_individual.ind_key=R.ind_key
AND R.action_key=DBA.dim_action.action_key
AND R.email_key=DBA.dim_email.email_key
AND DBA.dim_action.action_type = 'SOU'
AND DBA.dim_action.channel IN ('Online', 'Both', 'Offline')
AND DBA.dim_email.email_status = 'OK'
AND DBA.dim_individual.household_status = 'OK'
AND
(EXISTS (SELECT 1 from DBA.fac_brand_usage A
WHERE A.ind_key = R.ind_key
AND A.action_key = R.action_key)

OR EXISTS (SELECT 1 from DBA.fac_category_usage B
WHERE B.ind_key = R.ind_key
AND B.action_key = R.action_key)

OR EXISTS (SELECT 1 from DBA.fac_profiles A
WHERE A.ind_key = R.ind_key
AND A.action_key = R.action_key)

OR EXISTS (SELECT 1 from DBA.fac_life_trait A
WHERE A.ind_key = R.ind_key
AND A.action_key = R.action_key)

OR EXISTS (SELECT 1 from DBA.fac_appliance A
WHERE A.ind_key = R.ind_key
AND A.action_key = R.action_key)

OR EXISTS (SELECT 1 from DBA.fac_question A
WHERE A.ind_key = R.ind_key
AND A.action_key = R.action_key) )
GROUP BY
DBA.dim_action.action_group_descr

 

[JMCraig]

Hi,

Looks like you're using ASA. Most of the helpful folks who read this forum are AS Enterprise-types.

As I'm no ASA expert, I don't know what to suggest: now if you were using ASE, I'd use a temporary table to assemble the partial results--does ASA have temp tables? When you have a query this complex, try breaking it up into steps. You mention that UNION gives you multiple rows; put the key info somewhere temporary (even if you have to create a reporting table) and then select from that with appropriate group by).

You might also try the Sybase forum on ASA, you may get an answer you can use.

Best of luck,



J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[nickpark]

Can you not just move the EXISTS predicates into the join?

SELECT
  DBA.dim_action.action_group_descr,
  count (distinct R.ind_key)
FROM
  DBA.dim_individual,
  DBA.fac_ind_reaction R,
  DBA.dim_action,
  DBA.dim_email,

  DBA.fac_brand_usage A,
  DBA.fac_category_usage B,
  DBA.fac_profiles C,
  DBA.fac_life_trait D,
  DBA.fac_appliance E,
  DBA.fac_question F

WHERE
DBA.dim_individual.ind_key=R.ind_key
AND R.action_key=DBA.dim_action.action_key
AND R.email_key=DBA.dim_email.email_key
AND DBA.dim_action.action_type = 'SOU'
AND DBA.dim_action.channel IN ('Online', 'Both', 'Offline')
AND DBA.dim_email.email_status = 'OK'
AND DBA.dim_individual.household_status = 'OK'
AND 
(
  ( A.ind_key = R.ind_key AND A.action_key = R.action_key)
  OR
  ( B.ind_key = R.ind_key AND B.action_key = R.action_key)
  OR
  ( C.ind_key = R.ind_key AND C.action_key = R.action_key)
  OR
  ( D.ind_key = R.ind_key AND D.action_key = R.action_key)
  OR
  ( E.ind_key = R.ind_key AND E.action_key = R.action_key)
  OR
  ( F.ind_key = R.ind_key AND F.action_key = R.action_key)
)

GROUP BY
  DBA.dim_action.action_group_descr

Yuk!

--nick

 

[ScanX]

you solution is good nick, thank you !

and yeah J M I'm using ASA but I'm really new to this so I cant tell if it's possible to use temp tables, thanx for your help anyway !