May 5, 2008 at 3:50 pm
Hi all, I have the following 2 tables, paydataimport and pprwages:
CREATE TABLE [dbo].[PayDataImport](
[PersonNum] [varchar](15) NULL,
[PeriodEndDate] [datetime] NULL,
[PayCodeId] [int] NULL,
[WeekOfYear] [int] NULL,
[TempRate] [numeric](10, 5) NULL,
[Hours] [numeric](10, 5) NULL,
[Wages] [numeric](10, 5) NULL,
[Ll1Worked] [varchar](15) NULL,
[UsesMinWage] [int] NULL,
[HourlyRate_Calc] decimal (10,5) NULL
)
CREATE TABLE [dbo].[PPRWages](
[personnum] [varchar](15) NULL,
[weekofyear] [int] NULL,
[pprrate] [numeric](10, 5) NULL,
[periodenddate] [datetime] NULL
)
I need to either update the calculated field, 'HourlyRate_Calc' in PayDataImport, or calculate it on the fly in a SELECT statement as it is inserted into a historical table (payData). I have the following update. What i need to do is, if there is a match between paydataimport and pprwages on personnum, periodenddate, and weekofyear, then use the pprrate, if not, then use the formula shown below (don't worry about the formula, just the JOINS). Does this look correct with the LEFT JOINS and their order? Could I maybe do it differently/better? Thanks guys, and any questions and I'll clarify.
UPDATE p
SET P.HourlyRate_Calc = ISNULL(w.PPRRate, hr.hourlyrate)
FROM MQDW.dbo.ETM_PayDataImport p
LEFT JOIN MQDW.dbo.ETM_PPRWages w
ON (p.personnum = w.personnum) AND (p.PeriodEndDate = w.periodenddate) AND (p.weekofyear = w.weekofyear)
LEFT JOIN
(
select
p.PersonNum,
p.periodenddate,
p.weekofyear,
case
when sum(case when p.paycodeid in (111,2002,1001,101,117,2101,1401,119,2201,2202) then hours else 0 end) > 0 then
case
when
(
(
isnull(sum(case when p.paycodeid in (133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,150,151,152,153,154,155,156,157,158,159,160,163,166,167,168,401,501,701,801,2201,2202) and p.usesminwage is null then wages else 0 end),0) +
isnull(sum(case when p.paycodeid in (2) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (138) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (3) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (143) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (4) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (145) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (1,5,111,117,119,1401,2001,2002,2101) then wages else 0 end),0)
) / isnull(sum(case when p.paycodeid in (111,2002,1001,101,117,2101,1401,119,2201,2202) then hours else 0 end),1) -- Set to 1 will cause the rate to be extremely high, which will get overriden by 20 instead. This is correct
) > 100 then 20
else
(
(
isnull(sum(case when p.paycodeid in (133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,150,151,152,153,154,155,156,157,158,159,160,163,166,167,168,401,501,701,801,2201,2202) and p.usesminwage is null then wages else 0 end),0) +
isnull(sum(case when p.paycodeid in (2) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (138) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (3) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (143) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (4) and p.usesminwage is null then hours else 0 end) * max(case when p.paycodeid in (145) and p.usesminwage is null then temprate else 0 end),0) +
isnull(sum(case when p.paycodeid in (1,5,111,117,119,1401,2001,2002,2101) then wages else 0 end),0)
) / isnull(sum(case when p.paycodeid in (111,2002,1001,101,117,2101,1401,119,2201,2202) then hours else 0 end),1)
)
end
end as hourlyrate
from MQDW.dbo.ETM_PayDataImport p
GROUP BY
p.PersonNum,
p.periodenddate,
p.weekofyear
)
hr
ON (p.personnum = hr.personnum) AND (p.PeriodEndDate = hr.periodenddate) AND (p.weekofyear = hr.weekofyear)
May 21, 2008 at 3:04 am
The UPDATE...FROM syntax is not ANSI standard, and is only supported on SQL Server. It relies on the assumption that the criteria used to join two ses result in a 1-to-1 relationship between the sets.
In other words, unless...
ON (p.personnum = w.personnum) AND (p.PeriodEndDate = w.periodenddate) AND (p.weekofyear = w.weekofyear)
*and*
ON (p.personnum = hr.personnum) AND (p.PeriodEndDate = hr.periodenddate) AND (p.weekofyear = hr.weekofyear)
...result in exactly one row from the second and third tables for each row of the first table, the result of the UPDATE is unpredictable.
For a more accurate answer, please post sample data and expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 2:22 am
In update statement derived table is not allowed, alternate you can use temp table to store derived table record and then you join the temp table
bye
May 22, 2008 at 2:30 am
shamshudheen (5/22/2008)
In update statement derived table is not allowed, alternate you can use temp table to store derived table record and then you join the temp tablebye
That's simply not true. Subqueries are allowed in UPDATE statements, but when used in the SET clause a subquery must return a single scalar value.
And in order to secure a predictable result, the subquery must be properly correlated with the set affected by the UPDATE statement.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 5:06 am
Matija Lah (5/22/2008)
shamshudheen (5/22/2008)
In update statement derived table is not allowed, alternate you can use temp table to store derived table record and then you join the temp tablebye
That's simply not true. Subqueries are allowed in UPDATE statements, but when used in the SET clause a subquery must return a single scalar value.
And in order to secure a predictable result, the subquery must be properly correlated with the set affected by the UPDATE statement.
I AM NOT TALKING ABOUT SUB QUERY , I AM TALKING ABOUT DERIVED TABLE , MY SUGGESTION IS READ CAREFULLY AND POST DECISION
BYE
May 22, 2008 at 5:18 am
shamshudheen (5/22/2008)
Matija Lah (5/22/2008)
shamshudheen (5/22/2008)
In update statement derived table is not allowed, alternate you can use temp table to store derived table record and then you join the temp tablebye
That's simply not true. Subqueries are allowed in UPDATE statements, but when used in the SET clause a subquery must return a single scalar value.
And in order to secure a predictable result, the subquery must be properly correlated with the set affected by the UPDATE statement.
I AM NOT TALKING ABOUT SUB QUERY , I AM TALKING ABOUT DERIVED TABLE , MY SUGGESTION IS READ CAREFULLY AND POST DECISION
BYE
There's no need to scream.
Please, provide a repro that proves your point.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 6:06 am
EXAMPLE IS QUESTION WAS POSTED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!:D
May 22, 2008 at 6:31 am
Settle down.
You said:
In update statement derived table is not allowed ...
I say you are wrong, and here's the proof:
create tableA
(
AIDintnot null
,AValuevarchar(128)not null
)
create tableB
(
BIDintnot null
,BValuevarchar(128)not null
)
insertA
(
AID
,AValue
)
select1 as AID
,'this should be in B' as AValue
union all
select2
,'this should be in B'
insertB
(
BID
,BValue
)
select1 as BID
,'this should be replaced' as BValue
union all
select2
,'this should also be replaced'
select*
fromA
select*
fromB
updateB
setB.BValue = Derived.AValue
from(
selectAID
,replace(AValue, 'should be', 'is now') as AValue
fromA
) Derived
where(Derived.AID = B.BID)
select*
fromB
drop tableA
drop tableB
As you should plainly see, a derived table is used inside the UPDATE statement. And what is a derived table? It's a subquery used in the FROM clause.
Or are you referring to something completely different? If so - what? And, please, instead of capitalizing your vague responses provide a T-SQL sample.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 2:14 pm
Matija,
Way to go staying professional on this one and backing up your claims with code examples.
Code talks........BS walks.........
May 22, 2008 at 2:39 pm
Hi Matija,
I liked the way you kept your cool. I am not sure why you say that you should not try to Update the Alias? That is ANSI Standard. You claim it is not ANSI. That is the only part I do not agree with you. I was told by another MVP that Updating the Alias is the best way to go. His name is Bill Wunder. He was MVP for 2004,2005 and 2006. So could you please explain?
Roy
-Roy
May 22, 2008 at 2:49 pm
I am really new at sql code and have a question. So far I am able to select data from my as400 and import into sql 2000. This is the problem. I need to continue to select data, changes\updatesew data from the as400 and import into sql 2000. How do I compare my two tables, line by line, to see if there was a change\update to the data from as400 and get that change\update into sql 2000 table?
Any suggestions?
May 22, 2008 at 2:58 pm
How are you selecting the data from AS400 and importing to SQL Server? Do you need to update all columns or are you looking for certain column values. If it is the latter, try using Linked Server and Case statement. You should be able to do it.
It would have been better if you started a new thread for this topic:-)
-Roy
May 22, 2008 at 3:02 pm
You need a way to correlate the rows in the AS400 data to the rows in the SQL Server data. In other words - you need some combination of columns that uniquely identify each row.
Once you have that you can do a series of queries to update/synchronize your data. Assuming you could use a column called rowID to uniquely identify each row:
--updates
update SQLTABLE
set col1=as400.col1,
col2=as400.col2
--repeat as needed for all columns you might want to update
from SQLTABLE
inner join AS400 on SQLTABLE.rowid=AS400 .rowid
where not (SQLTABLE.col1=AS400 .col1 AND
SQLTABLE.col2=AS400 .col2
--repeat as needed for all columns you might want to update
)
--push new rows
INSERT SQLTABLE(rowid,col1,col2)
select rowid,col1,col2
from AS400
where not exists(select null from SQLTABLEwhere SQLTABLE.rowid=AS400.rowid)
--remove rows no longer on AS400
delete SQLTABLE
where not exists (select null from AS400 where SQLTABLE.rowid=AS400.rowid)
Of course - if you need things to match exactly - it might just be easiest to blow out everything in the SQL table and reimport the AS400 data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 22, 2008 at 3:05 pm
I can't imagine not remaining calm. 🙂 Must be the lawyer in me... 😉
Roy Ernest (5/22/2008)
Hi Matija,I liked the way you kept your cool. I am not sure why you say that you should not try to Update the Alias? That is ANSI Standard. You claim it is not ANSI. That is the only part I do not agree with you. I was told by another MVP that Updating the Alias is the best way to go. His name is Bill Wunder. He was MVP for 2004,2005 and 2006. So could you please explain?
Roy
Please, explain what you mean by "updating the alias".
The UPDATE...FROM syntax:
update %affected table%
set %affected column% = %referenced column%
from %referenced table%
where %correlational criteria%
%possible other criteria%
...is not ANSI syntax, it's proprietary to SQL Server.
And here's an example to show you why it's not ANSI and why this syntax requires special attention (i.e. making sure the affected set is correlated with the referenced set so that the query produces exactly one row per each row of the affected set):
create tableA
(
AIDintnot null
,AValuevarchar(128)not null
,primary key (AValue, AID)
)
create tableB
(
BIDintnot null
,BValuevarchar(128)not null
,primary key (BValue, BID)
)
insertA
(
AID
,AValue
)
select1 as AID
,'spank' as AValue
union all
select2
,'clank'
union all
select2
,'blank'
union all
select2
,'prank'
insertB
(
BID
,BValue
)
select1 as BID
,'this should be spank' as BValue
union all
select2
,'this should be clank'
select*
fromA
select*
fromB
updateB
setBValue = AValue
fromA
where(A.AID = B.BID)
select*
fromB
drop tableA
drop tableB
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 22, 2008 at 3:15 pm
L-Jeff,
Your question seems to be unrelated to the original poster's topic. You'd be better off starting a new thread if you want help from the community.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply