Newbie Question on searching text

I have a bulletin board application (written by a programmer) and want to
allow users to search the database for keywords/phrases. Doing a basic
search eats up the cpu like crazy, and I know there are some strategies on
how to get this done right.
Can someone provide me with an overview of how best to go about this? If
there are good articles/tutorials on this I'd greatly appreciate it also.
Btw, I'm running MS SQL server 2000 running dotnet.
Shabam,
A good place to start to understand SQL Server 2000 Full-text Search (FTS)
is Books Online (BOL) titles: "Full-Text Query Architecture", "Maintaining
Full-Text Indexes", "Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued
Functions" and especially "Full-Text Search Recommendations". You can also
search the BOL using "full text" (include the double quotes) via the BOL
Search tab for additional titles.
When you state that your "basic search" eats up CPU like crazy, are you
currently using FTS or are you using T-SQL LIKE or some other method? I've
also attached a SQL script file (Full Text Population Example.sql) that
demonstrates all aspects of using SQL FTS on the pubs database table
pub_info.
If you have additional questions, please post them.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
begin 666 Full Text Population Example.sql
M#0HM+0T*+2TM(%1O($5N86)L92!T:&4@.4'5B<R!$871A8F%S9 2!F;W(@.1G5L
M;"U497AT#0HM+0T*=7-E('!U8G,-"F=O#0IS<%]F=6QL=&5X=%]S97)V:6-E
M("=C;&5A;E]U<"<-"F=O#0IS<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE
M)R M+2 M+3X@.3D]413H@.3VYL>2!R=6X@.=&AI<R!/3D-%('!E<B!D871A8F%S
M92 A(2$-"F=O#0H-"BTM#0HM+2T@.5&\@.0W)E871E+U)E;6]V92!T:&4@.17AI
M<W1I;F<@.1G5L;"U497AT(%1A8FQE($EN9&5X+"!#871A;&]G( T*+2T@.(" @.
M268@.1G5L;"U497AT($EN9&5X(&5X:7-T<RP@.1%)/4"!T:&%T($EN9&5X+ T*
M+2T@.(" @.268@.1G5L;"U497AT($EN9&5X(&1O97,@.;F]T(&5X:7-T+"!#4D5!
M5$4@.=&AA="!);F1E>"X-"BTM#0IU<V4@.<'5B<PT*9V\-"DE&($]"2D5#5%!2
M3U!%4E19("@.@.;V)J96-T7VED*"=P=6)?:6YF;R<I+"=486)L94AA<T%C=&EV
M949U;&QT97AT26YD97@.G*2 ](#$-"D)%1TE.#0H@.(" @.<')I;G0@.)U1A8FQE
M('!U8E]I;F9O(&ES($9U;&PM5&5X="!%;F%B;&5D+"!D<F]P<&EN9R!&=6QL
M+51E>'0@.26YD97@.@.)B!#871A;&]G+BXN)PT*(" @.($5814,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)W!U8E]I;F9O)RP@.)V1R;W G#0H@.(" @.15A%0R!S<%]F=6QL
M=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V1R;W G#0I%3D0-"D5,4T4@.248@.
M3T)*14-44%)/4$525%D@.*"!O8FIE8W1?:60H)W!U8E]I;F9O)RDL)U1A8FQE
M2&%S06-T:79E1G5L;'1E>'1);F1E>"<I(#T@., T*0D5'24X-"B @.("!P<FEN
M=" G5&%B;&4@.<'5B7VEN9F\@.:7,@.3D]4($9U;&PM5&5X="!%;F%B;&5D+"!C
M<F5A=&EN9R!&5"!#871A;&]G+"!);F1E>" F($%C=&EV871I;F<N+BXG#0H@.
M(" @.15A%0R!S<%]F=6QL=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V-R96%T
M92<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P=6)?:6YF;R<L("=C
M<F5A=&4G+" G4'5B26YF;R<L("=54$M#3%]P=6)I;F9O)PT*(" @.($5814,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P=6)?:60G+" G861D
M)PT*(" @.($5814,@.<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P
M<E]I;F9O)RP@.)V%D9"<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P
M=6)?:6YF;R<L("=A8W1I=F%T92<-"D5.1 T*#0H-"BTM#0HM+2T@.069T97(@.
M16YA8FQI;F<@.)B!!8W1I=F%T:6YG(%1A8FQE<RP@.0V]L=6UN<R F($EN9&5X
M97,@.+2!3=&%R="!&=6QL(%!O<'5L871I;VX-"BTM#0IU<V4@.<'5B<PT*9V\-
M"D)%1TE.#0I3150@.3D]#3U5.5"!/3@.T*1$5#3$%212! 8F5G:6X@.9&%T971I
M;64-"D1%0TQ!4D4@.0&5N9"!D871E=&EM90T*4T54($!B96=I;B ]($-54E)%
M3E1?5$E-15-404U0#0I%6$5#('-P7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN
M9F\G+" G<W1A<G1?9G5L;"<@.+2T@.(D9U;&P@.0W)A=VPB#0HM+2!%6$5#( '-P
M7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN9F\G+" G<W1A<G1?:6YC<F5M96YT
M86PG("TM("));F-R96UE;G1A;"!#<F%W;"(-"BTM#0HM+2T@.5V%I="!F;W(@.
M8W)A=VP@.=&\@.8V]M<&QE=&4-"BTM#0I$14-,05)%($!S=&%T=7,@.:6YT+"!
M:71E;4-O=6YT(&EN="P@.0&ME>4-O=6YT(&EN="P@.0&EN9&5X4VEZ92!I;G0-
M"E-%3$5#5"! <W1A='5S(#T@.1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U
M8DEN9F\G+" G<&]P=6QA=&5S=&%T=7,G*0T*5TA)3$4@.*$!S=&%T=7,@./#X@.
M,"D-"D)%1TE.#0H@.(%=!251&3U(@.1$5,05D@.)S P.C P.C Q)R M+2!W86ET
M(&9O<B Q('-E8V]N9"!B969O<F4@.8VAE8VMI;F<@.1E0@.4&]P=6QA=&5S=&%T
M=7,N+BX-"B @.4T5,14-4($!S=&%T=7,@./2!&=6QL5&5X=$-A=&%L;V=0<F]P
M97)T>2@.G4'5B26YF;R<L("=P;W!U;&%T97-T871U<R<I#0I%3D0-"E-%5"!
M96YD(#T@.0U524D5.5%]424U%4U1!35 -"E=!251&3U(@.1$5,05D@.)S P.C P
M.C$U)R M+2!W86ET(&9O<B Q-2!S96-O;F1S(&EN(&]R9&5R('1O(&=E="!C
M;W)R96-T($94(%!R;W!E<G1Y(&EN9F\N+BX-"E-%5"! :71E;4-O=6YT(#T@.
M1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U8DEN9F\G+" G:71E;6-O=6YT
M)RD-"E-%5"! :V5Y0V]U;G0@./2!&=6QL5&5X=$-A=&%L;V=0<F]P97)T>2@.G
M4'5B26YF;R<L("=U;FEQ=65K97EC;W5N="<I#0I3150@.0&EN9 &5X4VEZ92 ]
M($9U;&Q497AT0V%T86QO9U!R;W!E<G1Y*"=0=6));F9O)RP@.) VEN9&5X<VEZ
M92<I#0I04DE.5"!#3TY615)4*&-H87(H,S I+"! 8F5G:6XL(#DI("L@.8VAA
M<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.0&5N9"P@..2D@.*R!C
M:&%R*# Y*2 K#0H@.(" @.("!#3TY615)4*&-H87(H,S I+"! 96YD("T@.0&)E
M9VEN+" X*2 K(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H8VAA<B@.S,"DL
M($1!5$5$249&("AH:"P@.0&)E9VEN+"! 96YD*2D@.*R!C:&%R*# Y*2 K#0H@.
M(" @.("!#3TY615)4*&-H87(H,S I+"!$051%1$E&1B H;6DL($!B96=I;BP@.
M0&5N9"DI("L@.8VAA<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.
M1$%4141)1D8@.*'-S+"! 8F5G:6XL($!E;F0I*2 K(&-H87(H,#DI("L-"B @.
M(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I=&5M0V]U;G0I("L@.8VAA<B@.P
M.2D@.*PT*(" @.(" @.0T].5D525"AV87)C:&%R*#$P*2P@.0&ME>4-O=6YT*2 K
M(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I;F1E
M>%-I>F4I#0I3150@.3D]#3U5.5"!/1D8-"D5.1 T*9V\-"@.T*+2T-"BTM+2!#
M;VYF:7)M(&%B;W9E(')E<W5L=',@.=VET:#H-"BTM#0I314Q%0U0@.<'5B7VED
M+"!P<E]I;F9O( T*"4923TT@.<'5B7VEN9F\@.5TA%4D4@.0T].5$%)3E,H<')?
8:6YF;RP@.)R)B;V]K*B(G*0T*9V\-"@.T*
`
end
|||can you post your query here?
Also is this an English language search?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
sql

newbie question

I have two datetimes dt1, dt2. dt2 is always greater than dt1
I want to do: dt2-dt1 and the result has to be written in the hh:mm:ss forma
t
Could anybody help me?Try,
declare @.dt1 datetime, @.dt2 datetime
declare @.i int
set @.dt1 = '2005-08-17T08:35:45.000'
set @.dt2 = getdate()
set @.i = datediff(second, @.dt1, @.dt2)
select
(@.i / 3600) as hh,
((@.i / 60) - ((@.i / 3600) * 60)) as mm,
@.i % 60 as ss
go
AMB
"DAMAR" wrote:

> I have two datetimes dt1, dt2. dt2 is always greater than dt1
> I want to do: dt2-dt1 and the result has to be written in the hh:mm:ss for
mat
> Could anybody help me?|||Hi
To get the time format u need to:
select convert(varchar(8), dt2-dt1, 108)
please let me know if this works
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"DAMAR" wrote:

> I have two datetimes dt1, dt2. dt2 is always greater than dt1
> I want to do: dt2-dt1 and the result has to be written in the hh:mm:ss for
mat
> Could anybody help me?|||DECLARE @.d1 DATETIME, @.d2 DATETIME, @.sd INT
SET @.d1 = '20050812 05:32:45'
SET @.d2 = '20050817 02:15:46'
SET @.sd = DATEDIFF(SECOND, @.d1, @.d2)
SELECT RTRIM(@.sd/3600) + ':'
+ RIGHT('0'+RTRIM((@.sd % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@.sd % 3600) % 60),2)
116:43:01
Note that this doesn't know whether one of the values is in an observed time
change, e.g. summer time or daylight savings time, so has the potential to
be an hour off if it crosses one of those boundaries.
"DAMAR" <DAMAR@.discussions.microsoft.com> wrote in message
news:45868E66-C51F-4F3B-9BCA-F97D6C6827CA@.microsoft.com...
>I have two datetimes dt1, dt2. dt2 is always greater than dt1
> I want to do: dt2-dt1 and the result has to be written in the hh:mm:ss
> format
> Could anybody help me?|||not at all:(
but thanx
"Chandra" wrote:
> Hi
> To get the time format u need to:
> select convert(varchar(8), dt2-dt1, 108)
> please let me know if this works
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "DAMAR" wrote:
>

Newbie : Need help in Sorting

I have written a query to find out the total worked hours & total dollars
for the employees who have worked in a particular time period .I need to sort them such that the employees with total amount =0 ( for the given period) come first followed by the employees who are to be paid .
At the end , I want to count the number of employees with total Amt = 0
& then the count of no. of employees who are to be paid . Here is the query

SELECT TAB1.EMP_ID ,
TAB1.EMP_NAME ,
TO_CHAR(ROUND((SUM(TAB1.WRKD_MINUTES))/60,2))
WRKD_HOURS,
TO_CHAR(ROUND(SUM(TAB1.WRKD_MINUTES *
TAB1.WRKD_RATE)/60)) TOTAL_AMT
FROM
( SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
(EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
HOUR_TYPE.HTYPE_NAME,
TIME_CODE.TCODE_NAME,
WORK_DETAIL.WRKD_WORK_DATE,
WORK_DETAIL.WRKD_MINUTES,
WORK_DETAIL.WRKD_RATE,
TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
CALC_GROUP.CALCGRP_NAME,
PAY_GROUP.PAYGRP_NAME
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID ) TAB1
GROUP BY EMP_ID,EMP_NAME

I have tried to group by Total Amount but it does not give the necessary order . Also , the database I am using is Oracle 8I enterprise version 8.1.7.7.0 .When I try to avoid TO_CHAR & just use ROUND , My query goes in a loop .You cannot group by the Total Amount, but you can order by it. Add this at the end:

ORDER BY total_amt

I don't understand your other problem with the ROUND.|||I had earlier tried ORDER BY .But it gives an error message ,
ERROR-937 at Line 1 Column 1 Message :ORA-00937:not a single-group group function.

I was trying to avoid TO_CHAR so that I can test the query by giving conditions such as TOTAL_AMT > 45 etc .When I use TO_CHAR , I cannot use this comparisons .|||If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .|||Originally posted by ritz1975
If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .
Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!|||I again gives the same oracle error msg which i posted .|||Originally posted by andrewst
Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!
I also don't understand why you are doing a select from a select - why not just this:

SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID
GROUP BY EMP_ID,EMP_NAME
ORDER BY TOTAL_AMT
?|||Originally posted by ritz1975
I again gives the same oracle error msg which i posted .
Try the version I just posted - or show me the code that you are actually running that gives the error message.|||That's because I have to use the other variables like Hourtpe ,Calcgroup,Paygroup etc . in the report view .I am working on the PL/SQL inside a product called WORKBRAIN ERM (Employee Relationship Management) .

Unless I use these variables in the SELECT ,I cannot define report criteria while designing the report .

I am trying your query now .|||I know nothing about WORKBRAIN ERM, but maybe it is part of the problem. I suggest you try running your query in SQL Plus first. Once it works there, THEN cut and paste it into WORKBRAIN ERM. If it then doesn't work, you know where the problem lies...

I understand that you need to join to various tables to restrict your query, but I still don't see why that should mean you need to use an inline view (SELECT FROM (SELECT FROM))|||I tried to run your code .But it now gives an error message ,
ERROR-918 at Line 1 Column 1 Message :ORA-00918:column ambiguously defined .

I changed the table order in the FROM statement to
EMPLOYEE,WORK_DETAIL,HOUR_TYPE,TIME_CODE,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

instead of EMPLOYEE,HOUR_TYPE,TIME_CODE,WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

but the result is the same .|||See here is the sample code of the view

CREATE OR REPLACE VIEW REPT_VIEW_IP_S_PAID_UNPAID
(
EMP_ID,
EMP_NAME,
FULLNAME,
HYPE_NAME,
TCODE_NAME,
WRKD_WORK_DATE,
WRKD_MINUTES,
WRKD_HOURS,
TOTAL_AMT,
CALCGRP_NAME,
PAYGRP_NAME
)
AS
SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
(EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
HOUR_TYPE.HTYPE_NAME,
TIME_CODE.TCODE_NAME,
WORK_DETAIL.WRKD_WORK_DATE,
WORK_DETAIL.WRKD_MINUTES,
TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
CALC_GROUP.CALCGRP_NAME,
PAY_GROUP.PAYGRP_NAME
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name <> 'UNPAID'
-- AND time_code.tcode_name <> 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID

I am using all those SELECT fields 'coz If I do not select all of them in the same order , I cannot define them under Create view statement|||I had forgotten to put the table aliases on the GROUP BY columns. Try this now:

SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID
GROUP BY EMPLOYEE.EMP_ID,EMPLOYEE.EMP_NAME
ORDER BY TOTAL_AMT|||That Works .Some how it still goes into a loop when I am using ROUND .I used TO_CHAR in fornt of both these variables & it then works .

Don't know why it does not work with a simple ROUND .I think I will need to check with the vendor .

Thanks a lot ,Andrewst ...looks like this should complete my query|||Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?|||Originally posted by ritz1975
Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?
Now that DOES need an inline view:

SELECT COUNT(*) FROM
( SELECT emp_id, SUM(...)
FROM ...
GROUP BY emp_id
HAVING SUM(...) = 0
)