with 
TREE1 (ouid, a_overhead) as 
( 
    select e.ouid, a_overhead
      from TABLE3 e
     where e.ouid = #SOME_PARAM1#
    union all
    select e.ouid, e.a_overhead
      from TABLE3 e
     inner join TREE1 n on e.a_overhead = n.ouid
),
TREE2 (a_name, a_id, a_parent, level) as 
( 
    select pc.a_name, pc.a_id, pc.a_parent, 1
      from TABLE4 pc
     where isnull(pc.a_status,10)<>70 
    and pc.a_id in (#SOME_PARAM2#)
    union all
    select pc.a_name, pc.a_id, pc.a_parent, level+1
      from TABLE4 pc
    inner join TREE2 n on pc.a_parent = n.a_id
    where isnull(pc.a_status,10)<>70
)
select (select a_name1 from TABLE5 eod where wpc.a_reg_orgname = eod.ouid) "orgName",
       pc.a_name "LK",
       pc.a_id "nLK",
       0 r,
       pc.a_name "LK0",
       null "PU",
       count(distinct wc.personouid) "LD"
  from TABLE1 wc
 inner join TABLE6 wpc on wpc.ouid = wc.personouid
                      and wpc.a_reg_orgname in (select ouid from TREE1) and isnull(wpc.a_status,10)<>70 
 inner join TABLE8 prnc on prnc.a_id = wc.a_name
 inner join TABLE4 pc on pc.a_id = prnc.a_cat and isnull(pc.a_status,10)<>70 
                     and pc.a_id in (select a_id from TREE2)
 where (((wc.A_DATE <= #SOME_PARAM4#) or (wc.A_DATE is null)) and ((wc.A_DATELAST is null) or (wc.A_DATELAST >= #SOME_PARAM3#)) and
       (ISNULL((select max(wr.A_DATE)
                  from TABLE6 wpc
                  left join TABLE11 wlpr on wlpr.FROMID = wpc.ouid and isnull(wlpr.a_status,10)<>70 
                  left join TABLE12 wr on wr.A_OUID = wlpr.TOID and isnull(wr.a_status,10)<>70 
                 where wpc.OUID = wc.personouid),
                #SOME_PARAM3#) >= #SOME_PARAM3#))
 group by pc.a_id, pc.a_name
union all
select (select a_name1 from TABLE5 eod where wpc.a_reg_orgname = eod.ouid) "orgName",
       null "LK",
       pc.a_id "nLK",
       rank() over(partition by pc.a_name order by sp.a_name) r,
       pc.a_name "LK0",
       sp.a_name "PU",
       count(distinct wc.personouid) "LD"
  from TABLE2 wasc
 inner join TABLE1 wc on wc.personouid = wasc.personouid
 inner join TABLE6 wpc on wpc.ouid = wc.personouid
                      and wpc.a_reg_orgname in (select ouid from TREE1) and isnull(wpc.a_status,10)<>70 
 inner join TABLE8 prnc on prnc.a_id = wc.a_name
 inner join TABLE4 pc on pc.a_id = prnc.a_cat
                     and pc.a_id in (select a_id from TREE2) and isnull(pc.a_status,10)<>70 
  left join TABLE9 scp on scp.a_category = pc.a_id
  left join TABLE10 sp on scp.a_preg = sp.ouid and isnull(sp.a_status,10)<>70 
                      and wasc.categoryouid = sp.ouid
 where (((wc.A_DATE <= #SOME_PARAM4#) or (wc.A_DATE is null)) and ((wc.A_DATELAST is null) or (wc.A_DATELAST >= #SOME_PARAM3#)) and
       (ISNULL((select max(wr.A_DATE)
                  from TABLE6 wpc
                  left join TABLE11 wlpr on wlpr.FROMID = wpc.ouid and isnull(wlpr.a_status,10)<>70 
                  left join TABLE12 wr on wr.A_OUID = wlpr.TOID and isnull(wr.a_status,10)<>70 
                 where wpc.OUID = wc.personouid),
                #SOME_PARAM3#) >= #SOME_PARAM3#))
   and sp.a_name is not null
 group by pc.a_id, pc.a_name, sp.a_name
 order by pc.a_id, pc.a_name desc, r

USING Parus.Business.Octopus;

DECLARE @InstalledProduct AS InstalledProduct;

SELECT DISTINCT InstalledProductUpdate.ModelDataBasePackage.Master.Identity
FROM InstalledProductUpdate
WHERE InstalledProductUpdate.Master = @InstalledProduct;

Shops ss;
ss.createFilter("NAME", EQUAL, "myShopName");
foreach(Shop s in ss)
{
  Customers cs = s.getCustomers();
  cs.createFilter("type", NOT_EQUAL, "debtor");
  foreach(Customer c in cs)
  {
     c.setReturnField("SURNAME");
  }
}
Query q = createQuery(ss);
Result r = q.execute();

myString = "select * from MYTABLE t where t.id = " + idCode;

P>Что думает по этому поводу народ?

create view InProgress(TaskTable @tablename) as
select * from @tableName where status in ("open", "assigned", "verifying")

select DatePart(w, saleDate) as WeekNumber, Sum(orderTotal) as WeekTotal from orders
 where DatePart(w, saleDate) between @startWeek and @endWeek
 group by DatePart(w, saleDate)
 order by 1 desc

select WeekNumber, WeekTotal from
(select DatePart(w, saleDate) as WeekNumber, Sum(orderTotal) as WeekTotal from orders
 group by DatePart(w, saleDate)
)
 where WeekNumber between @startWeek and @endWeek
 order by 1 desc

select o.*, ow.Name from orders o
join constraint Owner as ow// === inner join Employee on Employee.ID = Order.EmployeeID

foreign key constraint Owner(EmployeeID) references Employee(ID)

select o.*, o.Owner.Name from orders o

WITH XXX(WeekNumber, orderTotal) AS
(
    DatePart(w, saleDate), orderTotal from orders
)
SELECT WeekNumber, SUM(orderTotal) AS WeekTotal 
FROM XXX
WHERE WeekNumber between @startWeek and @endWeek
GROUP BY WeekNumber
ORDER BY WeekNumber

L>WITH XXX(WeekNumber, orderTotal) AS
L>(
L>    DatePart(w, saleDate), orderTotal from orders
L>)
L>SELECT WeekNumber, SUM(orderTotal) AS WeekTotal 
L>FROM XXX
L>WHERE WeekNumber between @startWeek and @endWeek
L>GROUP BY WeekNumber
L>ORDER BY WeekNumber
L>

L>Не убедился.

SELECT WeekNumber, SUM(orderTotal) AS WeekTotal 
FROM (SELECT DatePart(w, saleDate) as WeekNumber, orderTotal from orders) XXX
WHERE WeekNumber between @startWeek and @endWeek
GROUP BY WeekNumber
ORDER BY WeekNumber

create view allCustomers# as ... 
GO
select * from allCustomers# a1 join allCustomers# a2 on ...

create view InProgress(TaskTable @tablename) as
select * from @tableName where status in ("open", "assigned", "verifying")

where ((@startDate is null) or (orderDate>@startDate)

select o.*, Owner.Name from orders o
outer join Employee on Employee.ID = Order.EmployeeID

where Owner is not null

S> Хотя я бы предпочел сам Linq Я имею в виду всякие бодрые трансформации запроса на ходу. А то меня (и оптимизатор) со страшной силой бесят все эти

S>where ((@startDate is null) or (orderDate>@startDate)
S>

S>select o.*, Owner.Name from orders o
S>outer join Employee on Employee.ID = Order.EmployeeID
S>

select Owner.Name from orders where Owner is not null

S>> Хотя я бы предпочел сам Linq Я имею в виду всякие бодрые трансформации запроса на ходу. А то меня (и оптимизатор) со страшной силой бесят все эти

S>>where ((@startDate is null) or (orderDate>@startDate)
S>>

if @startDate is not null
then 
  define a as 
  select * from a where orderDate>@startDate

AVK>select Owner.Name from orders where Owner is not null
AVK>

S>create view allCustomers# as ... 
S>GO
S>select * from allCustomers# a1 join allCustomers# a2 on ... 
S>

def query1 = select * from customers where <some conditions>
def query2(x, y) = select ... from query1 where someTable.x = x and someTable.y = y
select query2(123, "yes")

S>>create view allCustomers# as ... 
S>>GO
S>>select * from allCustomers# a1 join allCustomers# a2 on ... 
S>>

VD>def query1 = select * from customers where <some conditions>
VD>def query2(x, y) = select ... from query1 where someTable.x = x and someTable.y = y
VD>select query2(123, "yes")
VD>

WITH EXPR AS
(SELECT /*ТУТ УСЛОВИЕ*/ FROM DUAL)
select * from EXPR e, TABLE_1 where e.result = 0
union all 
select * from EXPR e, TABLE_2 where e.result <> 0

P>>Но вот, что нет подобных функций, что возвращают Таблицы, как парамтризированные вьюхи...

CREATE OR REPLACE TYPE myRecord 
AS OBJECT
(
  A   INT,
  B   DATE,
  C   VARCHAR2(25)
)

CREATE OR REPLACE TYPE myRecordType
   AS TABLE OF myRecord
   
create function test
  return myRecordType
  PIPELINED
  as
  begin
     for i in (select * from mytable)
     loop
         pipe row(myRecord(i.a+1, i.b-1, i.c||'!'));
     end loop;
     return;
  end;
  
-- А потом использовать:
SELECT * FROM TABLE(test());

select sum(a) sum_a, case when sum_a > 0 then 'больше' else 'меньше' end 
from foo
group by b
having by sum_a <> 0
order by sum_a