Need Help with this Logic

[AFK1]

I need some help on writing a query that can help me to check for a particular record an dif the record exists, add 1 number to that record. Here is an example.
I have two cases.

Case# 1. I will get 4 Line# recors for same Pur# and same ItemID.(All fields are varchar)
-------------------------------------------------
Pur# Line# REL# ITMEID CustCode WH
------ ----- ---- ------- -------- ----
022234 0 00 0014111 1222 SSQ
022234 0 00 0014111 1222 SSQ
022234 0 00 0014111 1222 SSQ
022234 0 00 0014111 1222 SSQ
--------------------------------------------------------

So in my check i should be able to find if any of this record is in data base, if not I should be able to add these record like that( assume no record exists)

----------------------------------------------------------
Pur# Line# REL# ITMEID CustCode WH
------ ----- ---- ------- ------- ----
022234 1 00 0014111 1222 SSQ
022234 2 00 0014111 1222 SSQ
022234 3 00 0014111 1222 SSQ
022234 4 00 0014111 1222 SSQ
-----------------------------------------------------------

Lets say I have a record in DB like
-----------------------------------------------
Pur# Line# REL# ITMEID CustCode WH
------ ----- ---- ------- -------- ----
022234 1 00 0014111 1222 SSQ
--------------------------------------------------

than I should be able to write this records like that
-----------------------------------------------------
Pur# Line# REL# ITMEID CustCode WH
------ ----- ---- ------- -------- ----
022234 2 00 0014111 1222 SSQ
022234 3 00 0014111 1222 SSQ
022234 4 00 0014111 1222 SSQ
022234 5 00 0014111 1222 SSQ
-------------------------------------------------------

Case# 2. I will get 4 Line# recors for same Pur# and diff ItemID.(All fields are varchar)
-------------------------------------------------
Pur# Line# REL# ITMEID CustCode WH
------ ----- ---- ------- -------- ----
022234 0 00 00222sc 1222 SSQ
022234 0 00 0014122 1222 SSQ
022234 0 00 0321522 1222 SSQ
022234 0 00 07895211 1222 SSQ

so I want to write all these four record to DB by arranging the Line# keeping in mind if the line# is in DB for and same ITMEID for the same Pur#.

Here is my check query
If (@Line# = 0 ) Begin
Select @int_Count = Count(POD.Pur#)From POD with (Nolock),POH with (Nolock)
Where POH.Pur# = POD.Pur# And POH.REL# = POD.REL# And
POD.Pur# = @Pur# And POD.REL# = REL# And
POD.ITMEID = @ITMEID And POH.WH = 'A9' And
POH.CustCode = '18546'
If (@int_Count = 0) Begin --Part# not already on PO
Select @int_LineNumber = IsNull(Max(POD.LineNumber),0) + 1
From POD with (Nolock),POH with (Nolock)
Where POH.Pur# = POD.Pur# And POH.REL# = POD.REL# And
POD.Pur# = @Pur# And POD.REL# = @REL# And
POH.WH = 'A9' And POH.CustCode = '18546'
End Else--Part# already on PO
Begin
Select @int_LineNumber = Max(POD.LineNumber)
From POD with (Nolock), POH with (Nolock)
Where POD.Pur# = Pur# And POD.REL# = REL# And
POD.ASCItemId = @ITMEID And POH.Pur# = POD.Pur# And
POH.REL# = POD.REL# And POH.WH = 'A9' And
POH.CustCode = '18546'
End
End Else Begin
Select @int_Count = Count(POD.Pur#)
From POD with (Nolock), POH with (Nolock)
Where POD.Pur# = @Pur# And POD.REL# = @REL# And
POD.Line# = @Line# And POH.Pur# = POD.Pur# And
POH.REL# = POD.REL# And POH.WH = 'A9' And
POH.CustCode = '18546'
If @int_Count=0 Begin
Insert
End else begin
Update
End
End




Can someone please help..I will reqlly appreciate.

 

[philhege]

Hi AFK1,

I started writing this code and then I realized: without a primary key field, you're hosed. You do have a primary key field in the POD table, don't you?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[AFK1]

Yes, The primary key fields for POD are Pur#,REL# and Line# and index on ITMEID.