Recent content by barbarul2001

I need to point some weak points of O3 from Ideasoft. Anybody ,any ideas,hints pls. Thx Senior Developer / DBA

I know how hard can be a deadline problem so I help as soon as I can. I think this is what you want... SELECT Iif(DateAdd("d",t1.RenewalMonths*30,t1.RenewalDate)>date(),"Active", "Expired") FROM tbl t1 where t1.RenewalDate = ( select max(tbl.RenewalDate)...

It's strange what you say. The syntax looks ok and i don't know why this does not work. Do you really need those nulls? If not, it's simple: try to filtrate in where with cudefis not null . senior rdbms specialist

If you try to enforce unique constraint you could use NULL value, but only once. So, it is possible , but doesn't help you much. For your needs I recommend a trigger or to enforce integrity on the application level. senior rdbms specialist

fmorel you're right. I got carried away and I said some nonsenses. Ok this had nothing to do with indexes, what you wrote is a variation of what my and garluk's stuff . I have no comment for that. The presence of an index on a column does not guarantee it will be used. The following is a small...

You perform mathematical operations on the indexed(i think) column(s), so in both situations you will have table access full, so both are bed or equally good ;) senior rdbms specialist

What about this little one ? select TT1.*,compl,tries from ( SELECT A.id AS dep, sum(A.ant) AS sales, FROM A WHERE YEAR(A.d)=2002 GROUP BY A.id ) tt.1 LEFT JOIN ( select S.id AS dep ,COUNT(S.res) AS compl ,SUM(S.el) AS tries FROM S WHERE YEAR(s.d)=2002 GROUP BY s.id...

what about this one? SELECT A.id AS dep, SUM(distinct A.ant) AS sales, COUNT(S.res) AS compl, SUM(S.el) AS tries FROM A LEFT JOIN S ON A.id=S.id AND A.d=S.d WHERE YEAR(A.d)=2002 GROUP BY A.id; senior rdbms specialist

try this: select distinct s.sname from stocks s,order_detail od, order o where o.ono=od.ono and s.sno=od.sno group by s.sname having count(distinct o.cno)=(select count(c.cno) from customer c) senior rdbms specialist

in oracle is a litle bit complicated: --first a function: CREATE OR REPLACE FUNCTION CHECKN(word VARCHAR2) RETURN NUMBER IS i NUMBER; j NUMBER:=0; BEGIN FOR i IN 1..LENGTH(word) LOOP SELECT j+COUNT(*) INTO j FROM dual WHERE SUBSTR(word,i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9'); IF...

insert into Employee select 5562, John Smith , Dept.Dept_ID from Dept where Dept_Name='Engineering' web/sql developer

tdatgod look... IN Oracle first step: create a function: create or replace function wordcnt(sir varchar2) return number is i number; j number; Result number; begin j:=0; Result:=0; i:=1; while i<>0 loop j:=j+1; i:=instr(ltrim(rtrim(sir)),' ',1,j); if i+1<>instr(ltrim(rtrim(sir)),' ',1,j+1)...

Rectification for sybase: select top 1 company, user_id, maincontact from pt5_user xx having maincontact=max(maincontact) web/sql developer

try this select top 1 company, user_id, maincontact from pt5_user xx where maincontact=(select max(maincontact) from pt5_user where company = xx.company ) or this (it works in sybase) select top 1 company, user_id, maincontact from pt5_user xx where...

tdatgod was right, but not totally. Correct i think it is this: Select title from books where ltrim(rtrim(title)) not like ( '% %' ); Regards web/sql developer