INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INNER JOIN

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-03-19 : 13:36:27

OPEN CURS
FETCH NEXT FROM CURS INTO @REFERENCE_DATA, @CHECK_NUMBER, @CHECK_DATE, @INVOICE_AMT, @CHECK_RECON_STATUS
WHILE @@FETCH_STATUS = 0 BEGIN
SET @ERROR = ''
IF (SELECT COUNT(*) FROM CHECKS WHERE LEFT(PHNX_GUID,20) = @REFERENCE_DATA AND AMOUNT = @INVOICE_AMT) = 0 BEGIN --AND CHECKNUMBER = @CHECK_NUMBER
--CHECK DOESN'T EXIST
INSERT INTO ADVOC_CHECK_RETURN_FEED_AUDIT SELECT *, @RUN_DATE, 'RECORD DOES NOT EXIST ' FROM ADVOC_CHECK_RETURN_FEED WHERE REFERENCE_DATA = @REFERENCE_DATA AND CHECK_NUMBER = @CHECK_NUMBER AND CHECK_DATE = @CHECK_DATE AND INVOICE_AMT = @INVOICE_AMT AND CHECK_RECON_STATUS = @CHECK_RECON_STATUS
END
ELSE BEGIN
IF @CHECK_RECON_STATUS = 'C' BEGIN
BEGIN TRY
UPDATE CHECKS SET BANK_CLEAR_DATE = GETDATE(), CLEAR_DATE = GETDATE(), STATUS = '3', PRINT_TIME = GETDATE(), CHECKNUMBER = @CHECK_NUMBER WHERE LEFT(PHNX_GUID,20) = @REFERENCE_DATA AND AMOUNT = @INVOICE_AMT --AND CHECKNUMBER = @CHECK_NUMBER
END TRY
BEGIN CATCH
SET @ERROR = LEFT(ERROR_MESSAGE(),250)
INSERT INTO ADVOC_CHECK_RETURN_FEED_AUDIT SELECT *, @RUN_DATE, @ERROR FROM ADVOC_CHECK_RETURN_FEED WHERE REFERENCE_DATA = @REFERENCE_DATA AND CHECK_NUMBER = @CHECK_NUMBER AND CHECK_DATE = @CHECK_DATE AND INVOICE_AMT = @INVOICE_AMT AND CHECK_RECON_STATUS = @CHECK_RECON_STATUS
END CATCH
END
IF @CHECK_RECON_STATUS = 'O' BEGIN
BEGIN TRY
UPDATE CHECKS SET BANK_CLEAR_DATE = NULL, CLEAR_DATE = NULL, STATUS = '1', PRINT_TIME = @CHECK_DATE, CHECKNUMBER = @CHECK_NUMBER WHERE LEFT(PHNX_GUID,20) = @REFERENCE_DATA AND AMOUNT = @INVOICE_AMT --AND CHECKNUMBER = @CHECK_NUMBER
END TRY
BEGIN CATCH
SET @ERROR = LEFT(ERROR_MESSAGE(),250)
INSERT INTO ADVOC_CHECK_RETURN_FEED_AUDIT SELECT *, @RUN_DATE, @ERROR FROM ADVOC_CHECK_RETURN_FEED WHERE REFERENCE_DATA = @REFERENCE_DATA AND CHECK_NUMBER = @CHECK_NUMBER AND CHECK_DATE = @CHECK_DATE AND INVOICE_AMT = @INVOICE_AMT AND CHECK_RECON_STATUS = @CHECK_RECON_STATUS
END CATCH
END
--UPDATE THE CLAIM_FNCL_TRANS TABLE
BEGIN TRY
UPDATE CLAIM_FNCL_TRANS SET INSTRUMENT_NBR = @CHECK_NUMBER WHERE INSTRUMENT_KEY IN (SELECT PHNX_GUID FROM CHECKS WHERE LEFT(PHNX_GUID,20) = @REFERENCE_DATA AND AMOUNT = @INVOICE_AMT AND CHECKNUMBER = @CHECK_NUMBER)
END TRY
BEGIN CATCH
SET @ERROR = LEFT(ERROR_MESSAGE(),250)
INSERT INTO ADVOC_CHECK_RETURN_FEED_AUDIT SELECT *, @RUN_DATE, @ERROR FROM ADVOC_CHECK_RETURN_FEED WHERE REFERENCE_DATA = @REFERENCE_DATA AND CHECK_NUMBER = @CHECK_NUMBER AND CHECK_DATE = @CHECK_DATE AND INVOICE_AMT = @INVOICE_AMT AND CHECK_RECON_STATUS = @CHECK_RECON_STATUS
END CATCH
--UPDATE THE CLAIM_FT_PAYMENT TABLE

--UPDATE THE CLAIM_SUM TABLE

--INSERT THE RECORD INTO THE AUDIT TABLE
IF @ERROR = '' BEGIN
INSERT INTO ADVOC_CHECK_RETURN_FEED_AUDIT SELECT *, @RUN_DATE, 'ENTRY UPDATED SUCCESSFULLY' FROM ADVOC_CHECK_RETURN_FEED WHERE REFERENCE_DATA = @REFERENCE_DATA AND CHECK_NUMBER = @CHECK_NUMBER AND CHECK_DATE = @CHECK_DATE AND INVOICE_AMT = @INVOICE_AMT AND CHECK_RECON_STATUS = @CHECK_RECON_STATUS
END
END
FETCH NEXT FROM CURS INTO @REFERENCE_DATA, @CHECK_NUMBER, @CHECK_DATE, @INVOICE_AMT, @CHECK_RECON_STATUS
END
CLOSE CURS
DEALLOCATE CURS

I have the above script written where I populate a table ADVOC_CHECK_RETURN_FEED from a text file and based on the information I update certain fields.. I now have created another table called CHECK_CLEAR_DATE WHERE IT HAS A CHECKNUMBER AND CLEAR_DATE and what I want to do is

IF @CHECK_RECON_STATUS = 'C' BEGIN
BEGIN TRY
UPDATE CHECKS SET BANK_CLEAR_DATE = GETDATE(), CLEAR_DATE = GETDATE(), STATUS = '3', PRINT_TIME = GETDATE(), CHECKNUMBER = @CHECK_NUMBER WHERE LEFT(PHNX_GUID,20) = @REFERENCE_DATA AND AMOUNT = @INVOICE_AMT --AND CHECKNUMBER = @CHECK_NUMBER
END TRY

change BANK_CLEAR_DATE = GETDATE() TO BANK_CLEAR_DATE = CHECK_CLEAR_DATE.CLEAR_DATE but I do not know where to insert the INNER_JOIN command..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-19 : 14:13:30

I have one word for you: "set-based". or two hyphenated words but either way you have to forget the procedural processing and move to set-based statements for programing t-sql.

You never actually asked a question. But as far as I can understand all that code at the top populates [ADVOC_CHECK_RETURN_FEED]. And you have another table [CHECK_CLEAR_DATE] which also somehow got populated and now you want to update some columns in this table based on rows from the first table?

But perhaps you are looking for something like this. Not sure which columns are for which table but you should get the idea:


update c set
       c.BANK_CLEAR_DATE = GETDATE()
       ,c.CLEAR_DATE = GETDATE()
       ,c.[STATUS] = '3'
       ,c.PRINT_TIME = GETDATE()
from   ADVOC_CHECK_RETURN_FEED as a
inner join CHECK_CLEAR_DATE as c
       on     LEFT(c.PHNX_GUID,20) = a.REFERENCE_DATA 
       AND    c.AMOUNT = a.INVOICE_AMT
       AND    c.CHECKNUMBER = a.CHECK_NUMBER
where  c.CHECK_RECON_STATUS = 'C'

Be One with the Optimizer
TG

nurunseo
Starting Member

6 Posts

Posted - 2013-03-24 : 02:57:17

Hello friends!
Thanks fro shearing.................
I mean it will help to me..........................................

http://paneuromix.com/en
http://paneuromix.com/nootropics.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources