Can't get query syntax right....

[brettgab]

There are two problems with the query below...

1. I'm trying to populate the field 'Territory_Code' with '900' if the Conn.Rep_Code exists in Table CCR, and populate it with the Conn.Territory_Code if it doesn't. I've got the first part working (populating certain records with '900') but you're obviously not allowed to use the word NOT after the 'WHEN' clause, so all the other records are being populated with NULL. Any suggestions as to how I can get this working ?

The second problem...This query uses GROUPING and I do NOT want to group on Rep_Code, but I need to test for Rep_Code in the above test, and it requires me to put it in the list.

DO I need to use a subquery ? I'm having REAL trouble getting subqueries to work, I'm struggling to understand them. I've read Transact SQL help and seen some examples but they tend to keep it simple and don't mix subselects and grouping. Any way around this ?...otherwise I'm up shit creek without a paddle.

SELECT Conn.Store, CASE NConn.Rep_Code WHEN (SELECT
CCR.Rep_Code FROM CallCentreReps CCR WHERE CAST
(NConn.Rep_Code AS VarChar) = CAST(CCR.Rep_Code AS VarChar))

THEN '900' WHEN NOT (SELECT Rep_Code FROM CallCentreReps
CCR WHERE NOT CAST(NConn.Rep_Code AS VarChar) = CAST
(CCR.Rep_Code AS VarChar)) THEN Conn.Territory_Code END AS
Territory_Code,

Conn.Profit, Conn.Profit_Target, Conn.LBU, Conn.LBSO,
Conn.FRU, Conn.FRN, Conn.FRunner_Target,

Conn.FR_Perf, Conn.PSU, Conn.PSN, Conn.PSaver_Target,
Conn.PS_Perf, Conn.UGPlanInc, Conn.OtherNew, Conn.TotalNew,
Conn.TotalUG, Conn.Handsets,

IsNull(Conn.Profit,0) + Sum(IsNull(NConn.Profit,0)) AS
Total_Profit,

CASE Conn.Profit_Target WHEN 0 THEN 0 ELSE (IsNull
(Conn.Profit,0) + Sum(IsNull(NConn.Profit,0))) /
Conn.Profit_Target END AS Profit_Perf

FROM ConnSummMTD Conn LEFT JOIN VW_Rep_Non_Conn_Profit
NConn ON Conn.Territory_Code = NConn.Territory_Code

WHERE NConn.so_invoice_date >= '01-Sep-2001' AND
NConn.so_invoice_date <= '17-Sep-2001'

GROUP BY
Store,NConn.Territory_Code,Rep_Code,Conn.Profit,Profit_Targe
t,LBU,LBSO,FRU,FRN,FRunner_Target,FR_Perf,PSU,PSN,PSaver_Tar
get,PS_Perf,UGPlanInc,OtherNEW,TotalNew,TotalUG,Handsets

 

[Tim1]

Hiya, quite a few bits and pieces, so I will try to help where I can :

1. I'm trying to populate the field 'Territory_Code' with '900' if the
Conn.Rep_Code exists in Table CCR, and populate it with the
Conn.Territory_Code if it doesn't. I've got the first part working
(populating certain records with '900') but you're obviously not allowed
to use the word NOT after the 'WHEN' clause, so all the other records
are being populated with NULL. Any suggestions as to how I can get this
working ?

Why don't you try doing this in two bashes:

UPDATE table
SET territory_code = '900'
FROM table t1,
WHERE EXISTS (SELECT 1
FROM CCR t2
WHERE t1.Rep_Code = t2.Rep_Code)

UPDATE table
SET territory_code = Conn.Territory_Code
FROM table1 t1,
CCR Conn
WHERE NOT EXISTS (SELECT 1
FROM CCR t2
WHERE t1.Rep_Code = t2.Rep_Code)


I will have a think about your other questions,

HTH

Tim

 

[brettgab]

Thanks Tim for you efforts, however this is not what I want. The query is simply a 'select' query returning (ultimately) a recordset to display. No updating is done as such, I still want the Territory_Code to remain the way it is on file, but I want the query to return a different value (as specified above)