topic Re: SubQuerry Not being executed in Application Development and Automation Discussions

SubQuerry Not being executed

Former Member — Sat, 03 Dec 2011 12:37:19 GMT

Hi Gentelmen;

I have posted this question in the Test and Playground Forum but no one has responded.

Any of you experts in this forum may be able to help me please...

I have this problem where I am selecting records to display on the report from table customer and trying to count how many booking are there for each customer in booking table based on the customer id from booking table.

here is my query code below...

select id name city custtype discount into (wa-scid, wa-scname, wa-sccity, wa-scctype, wa-scdisc) from customer

  where city = 'CityName' and
  country = 'CountryName' and exists ( 
  select count( distinct custid ) as wa-cnt from bookings group by custid ).

  WRITE: /1 wa-scid, 11 wa-scname, 40 wa-sccity, 70 wa-scctype, 78 wa-scdisc,'%', 90 wa-cnt.

  endselect.

I have tried to debug the issue and seems like the sub query is not being executed. The reason i say that because the value of wa-cnt which is a counter of type i is not changing.

Any one can shed some light in this matter/problem please...

Many Thanks.

-hisheeraz

Re: SubQuerry Not being executed

Former Member — Sun, 04 Dec 2011 19:20:30 GMT

You are using AS addition in the subquery to determine whether the subquery is being executed or not, and I think this is wrong. AS addition has only meaning when your using wither INTO or APPENDING, it wouldn't change the value of wa-cnt.

I suggest you to try to change the subquery to

and exists ( select count( * ) from bookings ).

As you're using only the COUNT aggregation expression int the subquery, a GROUPING is not necessary.

Regards

Re: SubQuerry Not being executed

Former Member — Mon, 05 Dec 2011 05:54:54 GMT

why dont you just break it down to two queries?

Re: SubQuerry Not being executed

Former Member — Mon, 05 Dec 2011 10:38:15 GMT

Thanks for the reply Julio Almeida

that syntax of yours is good and i tried that already before your reply but my problem is i have no idea how can i place the value of that count into something ( a variable ) to display on the report screen. or if i donot hold that count() value to a variable then how can i display that subquery count() value on the report.

could you assist in this matter further please ?

@Soumyaprakash M... having two querries works and give me the required result but i am not allowed to do so in that order.

thanks heaps.

Re: SubQuerry Not being executed

Former Member — Mon, 05 Dec 2011 13:07:11 GMT

read your documentation on subquery or see tips and tricks in SE30. You cannot retain any values from that subquery....subqueries are intended to check only the existence (or does not exist) of a value or condition in a separate table, as part of the decision process to retain (or drop) values from the first table.

This is much faster than FAEI (For all entries in) or JOIN, but has this particular limitation!...

Re: SubQuerry Not being executed

Former Member — Mon, 05 Dec 2011 13:11:45 GMT

If you want to hold the subquery value, then you have to split it in two separate queries. As BreakPoint said, the subquery is not intented to change any variables, but to check existence of values in other table.

Re: SubQuerry Not being executed

Former Member — Mon, 05 Dec 2011 14:19:15 GMT

Thanks to all the experts who answered and tried to contribute towards my solution. regards.

-hisheeraz