Help with Stored procedure please...

[nc297]

How do I write this?

if max(juris_mvt_seq_num) and org_id in the t2juristest table not like 'p%' then use the org_id1 in the t2dibtest2 table where
max(dib_mvt_seq_num) to determine the location.

I got this to get the max(juris_mvt_seq_num) from the t2juristest table:

select max(juris_mvt_seq_num) as juris_mvt_seq_num, cos
from t2dibtest2 t
where org_id not like 'p%'
group by cos

Since it's not like 'P%' I need to grab the org_id1 column in the other table (t2dibtest2) to tell me the location. How do I do that?

CREATE TABLE [dbo].[T2dibtest2](
[T2id] [int] IDENTITY(1,1) NOT NULL,
[DOC] [varchar](3) NULL,
[CLM] [varchar](6) NULL,
[COS] [varchar](6) NULL,
[APP_RCPDT] [datetime] NULL,
[DIB_MVT_SEQ_NUM] [smallint] NULL,
[LOREC4] [char](3) NULL,
[ORG_ID1] [char](3) NULL,
[DIB_MVT_TYP] [char] (1) NULL)

Insert into T2dibtest2
Select '593', '001257', '001257', '2011-04-28', '4', 'S86','S86', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '3', 'S86', 'S07', 'T' union all
Select '593', '001257', '001257', '2011-04-28', '2', 'S86', 'S07', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '1', 'S86', '093', 'T'


CREATE TABLE [dbo].[T2Juristest](
[CLM] [char](6) NOT NULL,
[COS] [char](6) NOT NULL,
[JURIS_MVT_SEQ_NUM] [smallint] NOT NULL,
[ORG_ID] [varchar](3) NOT NULL,
[JURIS_MVT_TYP] [char](1) NOT NULL,
[t2jurisid] [int] IDENTITY(1,1) NOT NULL
)


Insert into T2Juristest
Select '001257', '001257', '1', '093', 'T' union all
Select '001257', '001257', '2', '539', 'R'

 

[nc297]

I tried it but got these errors:

Msg 207, Level 16, State 1, Line 37
Invalid column name 'Juris_mvt_typ'.
Msg 207, Level 16, State 1, Line 51
Invalid column name 'juris_mvt_typ'.
Msg 207, Level 16, State 1, Line 53
Invalid column name 'ORG_ID'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'ORG_ID'.
Msg 207, Level 16, State 1, Line 105
Invalid column name 'JURIS'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'JURIS'.

Do you think it's because the table t2pendjuris doesn't have an alias like this one:

SELECT CLM,
MAX(DIB_MVT_SEQ_NUM) AS DIB
FROM T2dibPend AS d2
-- WHERE CLM = '001257'
GROUP BY CLM) AS d1

SELECT CLM, MAX(JURIS_MVT_SEQ_NUM) AS JURIS
FROM T2PendJuris
GROUP BY CLM) AS j

The above one has d2 after the t2dibpend. I tried to put d3 on it but got other errors. Should I put the j next to T2pendJuris?

 

[Qik3Coder]

you should always give your inner/derived query tables an alias, so that the db doesn't get confused.

Select col3 
from tablea a 
join (
Select [!]a_i[/!].col1, max([!]a_i[/!].col1) maxCol
from tablea [!]a_i[/!]) as a2 on
a.col1 = a2.col1
where a.col2 = 42

I had said to disregard my initial comment about removing the max. I hadn't noticed that you gave the juris columns a different name than the source column, so it looked like you were doing a max(max(col1)), when you aren't.

Lod

You've got questions and source code. We want both!