query

Question

Post reply

query

khushbu

SSCarpal Tunnel

Points: 4591
More actions
March 30, 2010 at 6:32 am

#217207

help me write below query:
a start will also help 🙂
if drverri.despmce = space
drverri.deinstn = dropera.deinstn and
drverri.deopnnr = dropera.deopnnr and
drverri.deregjr = dropera.deregjr and
drverri.devawpn = dropera.devawpn and
drverri.deverrn = dropera.deverrn and
dropera.deinstn = drspect.deinstn and
dropera.despece = drspect.desptce and
drverri.dedaver between drspect.dedatin and drspect.dedaten
 spmeuitv = drspect.despmce
else
 spmeuitv = drverri.despmce

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

not enough info, khushbu ; the pseudo code you posted....is that an UPDATE? a SELECT? what is the question?

it almost looks like an attempt at an UPDATE to me, but I can't tell;

I can infer that there are three tables in question: drverri dropera and drspect

but beyond that, i'n not sure what you wanted to accomplish.

khushbu (3/30/2010)
help me write below query:
a start will also help 🙂
if drverri.despmce = space
drverri.deinstn = dropera.deinstn and
drverri.deopnnr = dropera.deopnnr and
drverri.deregjr = dropera.deregjr and
drverri.devawpn = dropera.devawpn and
drverri.deverrn = dropera.deverrn and
dropera.deinstn = drspect.deinstn and
dropera.despece = drspect.desptce and
drverri.dedaver between drspect.dedatin and drspect.dedaten
? spmeuitv = drspect.despmce
else
? spmeuitv = drverri.despmce

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 2

it's not entirely clear what you want;

if it is a query with a where clasue the format is

select * from table

where <where clause>

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 3

would probably candidate for update.. but since it has to deal with billions records I was just thinking of doing it with select with some other fields being populated.

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 4

if U want to use if statement in the query go for case...

Case

when ( drverri.despmce = space

drverri.deinstn = dropera.deinstn and

drverri.deopnnr = dropera.deopnnr and

drverri.deregjr = dropera.deregjr and

drverri.devawpn = dropera.devawpn and

drverri.deverrn = dropera.deverrn and

dropera.deinstn = drspect.deinstn and

dropera.despece = drspect.desptce and

drverri.dedaver between drspect.dedatin and drspect.dedaten )

Then

spmeuitv = drspect.despmce

ELSE

spmeuitv = drverri.despmce

END

AS SPmeuitvValue

Use it in either select or Update

Lowell SSC Guru Points: 323518 More actions · Answer 5

this is syntactically incorrect, whether a select or an update....paste it in SSMS and see for your self.

Sasidhar Pulivarthi (3/30/2010)
if U want to use if statement in the query go for case...
Case
when ( drverri.despmce = space
drverri.deinstn = dropera.deinstn and
drverri.deopnnr = dropera.deopnnr and
drverri.deregjr = dropera.deregjr and
drverri.devawpn = dropera.devawpn and
drverri.deverrn = dropera.deverrn and
dropera.deinstn = drspect.deinstn and
dropera.despece = drspect.desptce and
drverri.dedaver between drspect.dedatin and drspect.dedaten )
Then
spmeuitv = drspect.despmce
ELSE
spmeuitv = drverri.despmce
END
AS SPmeuitvValue
Use it in either select or Update

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!