convert query sql to SQLServer sql

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.

Re: convert query sql to SQLServer sql

Greetings,

since your query is not linked to any additional tables and there is no
Where clause, then you are updating your table to just one value. You
can simplify your query by extracting the value first and then passing
that value into your Update query

Value = DLookup or DSum, Dcount, ...

Update tblx Set fld1 = value

or

Update tblx Set fld1 = value where fldy is not null

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: convert query sql to SQLServer sql

"bobh" wrote in message
news:1193944810.979643.154960@d55g2000hsg.googlegroups.com.. .
> Hi All,
>
> I have this query which updates a field based on the result of an IIF
> statement. The table is on SQLServer and I'm linked to it and it has
> many records and will take Access a very long time to run so I'm
> thinking I can make this query a pass-thru query and I hope someone
> can write the proper SQLServer SQL for this Access SQL
> I know I have to change the IIF to Case When Else but I haven't been
> able to get it figured out yet.
>
> UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
> IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
> IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
> IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
> EOB2Line,tblHospLineFinal!EOB1Line)));
>
> thanks
> bobh.
>

It may be quicker run 5 querys using the test in you IIF for the WHERE
clause.

But why would you want to copy data from 1 column of a table to another for
all rows of your table? Unless you were reformatting the data?

Re: convert query sql to SQLServer sql

UPDATE tblHospLineFinal

SET tblHospLineFinal.RemarkCode =
CASE


IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
EOB2Line,tblHospLineFinal!EOB1Line)));



On Nov 1, 1:20 pm, bobh wrote:

> Hi All,
>
> I have this query which updates a field based on the result of an IIF
> statement. The table is on SQLServer and I'm linked to it and it has
> many records and will take Access a very long time to run so I'm
> thinking I can make this query a pass-thru query and I hope someone
> can write the proper SQLServer SQL for this Access SQL
> I know I have to change the IIF to Case When Else but I haven't been
> able to get it figured out yet.
>
> UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
> IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
> IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
> IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
> EOB2Line,tblHospLineFinal!EOB1Line)));
>
> thanks
> bobh.

Re: convert query sql to SQLServer sql

UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode =
CASE
WHEN ResubEOR is not null THEN ResubEOR
WHEN EOB3Line is not null then EOB3Line
WHEN EOB2Line is not null then EOB2Line
ELSE EOB1LINE
end


OR:


UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode = coalesce
(ResubEOR,EOB3Line,EOB2Line,EOB1LINE,'Unknown')



Chris Nebinger





On Nov 1, 1:20 pm, bobh wrote:
> Hi All,
>
> I have this query which updates a field based on the result of an IIF
> statement. The table is on SQLServer and I'm linked to it and it has
> many records and will take Access a very long time to run so I'm
> thinking I can make this query a pass-thru query and I hope someone
> can write the proper SQLServer SQL for this Access SQL
> I know I have to change the IIF to Case When Else but I haven't been
> able to get it figured out yet.
>
> UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
> IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
> IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
> IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
> EOB2Line,tblHospLineFinal!EOB1Line)));
>
> thanks
> bobh.

Re: convert query sql to SQLServer sql

Both these approaches were returning nulls in the RemarkCode field.
After playing with these for a long while and not having any success
at getting them to work and I believed they should have worked. I
finally discovered that the SQL table that the dba setup on the
SQLServer has 'Allow zero lenght strings' set to yes on all the text
fields. After writing some vba code and checking for nulls it didn't
find any nulls changed the code to find Len(RemarkCode)>0 it didn't
find any so, I changed your
Case logic to say
WHEN Len(ResubEOR)>0 THEN ResubEOR

Bingo! it worked perfectly........ thanks for your help and
steering me in the right direction.
bobh.

On Nov 1, 9:00 pm, "chris.nebin...@gmail.com"
wrote:
> UPDATE tblHospLineFinal
>
> SET
> tblHospLineFinal.RemarkCode =
> CASE
> WHEN ResubEOR is not null THEN ResubEOR
> WHEN EOB3Line is not null then EOB3Line
> WHEN EOB2Line is not null then EOB2Line
> ELSE EOB1LINE
> end
>
> OR:
>
> UPDATE tblHospLineFinal
>
> SET
> tblHospLineFinal.RemarkCode = coalesce
> (ResubEOR,EOB3Line,EOB2Line,EOB1LINE,'Unknown')
>
> Chris Nebinger
>
> On Nov 1, 1:20 pm,bobh wrote:
>
>
>
> > Hi All,
>
> > I have this query which updates a field based on the result of an IIF
> > statement. The table is on SQLServer and I'm linked to it and it has
> > many records and will take Access a very long time to run so I'm
> > thinking I can make this query a pass-thru query and I hope someone
> > can write the proper SQLServer SQL for this Access SQL
> > I know I have to change the IIF to Case When Else but I haven't been
> > able to get it figured out yet.
>
> > UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
> > IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal!ResubEOR,
> > IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal!EOB3Line,
> > IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal!
> > EOB2Line,tblHospLineFinal!EOB1Line)));
>
> > thanks
> >bobh.- Hide quoted text -
>
> - Show quoted text -