SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INNER JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 03/19/2013 :  13:36:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 03/19/2013 :  14:13:30  Show Profile  Reply with Quote
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
Go to Top of Page

nurunseo
Starting Member

Bangladesh
6 Posts

Posted - 03/24/2013 :  02:57:17  Show Profile  Reply with Quote
Hello friends!
Thanks fro shearing.................
I mean it will help to me..........................................


http://paneuromix.com/en
http://paneuromix.com/nootropics.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000