Can I create a subquery statement inside query wizard?

rbuser

Hi,
I want to create subquery like this ,

select y.isturu,f.ozelkod,f.ad,
(select ad from skn_sabittanim where grupkod='ISTURU' and kod =y.isturu) as
isturuad,
(select count(*) from skn_yuk where firmaid=y.firmaid and yuklemetip=0) as
grupaj,
(select count(*) from skn_yuk where firmaid=y.firmaid and yuklemetip=1) as
komple,
(case ozelkod when null then 'Di?er' when '' then 'Di?er' else ozelkod end)
as ozelkodad
from skn_yuk y
left outer join sbr_firma f on f.firmaid = y.firmaid
group by y.firmaid,y.isturu,f.ozelkod,f.ad
order by isturu,f.ozelkod,f.ad

but I cant use this sub query statement for any way.
(select count(*) from skn_yuk where firmaid=y.firmaid and yuklemetip=0) as
grupaj )etc.

When I try to edit sql , I cant use sort,search, calc etc. properties of
query . I try to write this statement on expression (Calc)
but its not work .
Like this case ,I need to use subquery (on DADE end - user platform ) but
how ?

Thanks for yours helps,
Kemal ?oruhlu

nicocizik

Hi Kemal,

If you are using the ReportBuilder 7, you can add subqueries into the Select
statement using the "Calcs" tab of the Query Designer. Simply add a
calculation field, set the Function type to "Expression" and type your
subselect statement into the Expression box.

--

rbuser

Thanks, I write my subselect in Expression area but I have got another
problem now. So,


Select m.workdate,
(select sum(totalprice) from my mytable2 where recordid = m.recordid )
from mytable m

in this query, I create an autosearch field for workdate field (beetween
dates) for end user enter the date range and get the report.
But this date range is working for only "mytable m" table. But I want to
filter my subquery with this range .
in the end my query must be like that,

Select m.workdate,
(select sum(totalprice) from my mytable2 where recordid = m.recordid and
workdate between date1 and date 2 )
from mytable m
where workdate between date1 and date 2

How can I apply autosearch "where" statement into subquery?

Thanx,
Kemal

nicocizik

Hi Kemal,

Sorry, AutoSearch is not currently available for subqueries in ReportBuilder
using DADE.

--