Line Lengths - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Line Lengths

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 10:53:47

I have a query that many of the lines are too wide to fit the screen. (658 lines to be exact.)

Is there an automated way to split those into two lines, so that each line fits in the screen view?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 11:41:20

Are you looking for SUBSTRING?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 13:00:54

Shouldn't this be handled on the front end?

Tara

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 13:15:55

No this is more like a text editor issue. I need to shorten the lines so I can view complete lines of the query in QA. I am sure there are programmers who deal with this everyday. I have a lot of queries that were written before I came here, and lets just say that are not very readable.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 13:18:13

quote:
Originally posted by TSQLMan

No this is more like a text editor issue. I need to shorten the lines so I can view complete lines of the query in QA. I am sure there are programmers who deal with this everyday. I have a lot of queries that were written before I came here, and lets just say that are not very readable.

Huh?

What version are you using? Don't you have horizontal scroll bars? What about putting it in grid form?

Why don't you cut and paste one of thos bad boys here....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 13:19:16

Also, take a look at Tools..Options..Results tab in Query Analyzer.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 13:27:05

Hey how cool is thw wav option at the end of a query batch...betcha it gets real annoying, real fast...

just turned the tada wav on...people should be complaining very shortly...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 13:58:25

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE _SP_APOPENREPORT

@COMPANY CHAR(5)

AS

TRUNCATE TABLE APOPEN

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,
APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'MEC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT,
SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+
'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),

APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT, APV.VEND_TYPE_CD

FROM DATA_005.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_005.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,
APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'OVCC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT,
SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+
'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4)

,APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,APV.VEND_TYPE_CD

FROM DATA_002.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_002.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'AEC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_055.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_055.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'KRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_033.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_033.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'TACC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_061.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_061.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'MCMI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_011.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_011.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'CRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_020.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_020.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'MCMC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_030.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_030.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'TACSC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_021.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_021.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'ALRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_042.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_042.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'ERI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_031.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_031.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'ETI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_032.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_032.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'PACI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_035.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_035.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'WVRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_036.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_036.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'CCC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_034.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_034.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'SCCC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_039.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_039.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'ARLI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_040.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_040.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'TCSI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_043.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_043.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'PCLP' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_038.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_038.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'OCCI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_037.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_037.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'CASI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_022.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_022.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'PFI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_023.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_023.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'ACCI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_051.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_051.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'BCI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_056.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_056.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'KTCI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_053.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_053.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'OVRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_001.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_001.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'CLC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_057.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_057.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'SRI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_010.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_010.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'TOVTC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_003.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_003.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'THVRC' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_004.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_004.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'CSG' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_062.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_062.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'PTI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_014.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_014.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'UMCO' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_012.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_012.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

INSERT INTO APOPEN

(COMPANY,VENDORNUMBER,VENDORNAME,PAYAMOUNT,AMOUNT,DUEDATE,OPENCLOSECODE,INVOICENUMBER,APOPENDATE,FULLYPAIDDATE,VENDORTYPE)

SELECT 'MTCI' AS COMPANY,APO.VEND_NO,APV.VEND_NAME,APO.PAY_AMT,APO.AMT, SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.DUE_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.DUE_DT),1,4),APO.OPN_CLOS_CD,APO.INV_NO, SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),5,2)+'/'+SUBSTRING(CONVERT(CHAR(10),APO.AP_OPN_DT),7,2)+'/'+SUBSTRING(CONVERT(CHAR,APO.AP_OPN_DT),1,4),APO.FULLY_PAID_DT,

APV.VEND_TYPE_CD

FROM DATA_015.DBO.APOPNFIL_SQL APO

INNER JOIN DATA_015.DBO.APVENFIL_SQL APV ON APO.VEND_NO = APV.VEND_NO

WHERE APO.AP_OPN_DT <= 20050131 AND APV.VEND_TYPE_CD <> 'INTCO' AND (APO.OPN_CLOS_CD = 'O'

OR APO.FULLY_PAID_DT > 20050131) AND (APO.AMT <> 0 OR APO.PAY_AMT <> 0)

CREATE TABLE #TP

(
COMPANY CHAR(5),
VENDORNUMBER CHAR(12),
VENDORNAME CHAR(40),
PARTIALPAYMENT MONEY,
CURRENTAMOUNT MONEY,
ZEROTOTHIRTY MONEY,
THIRTYTOSIXTY MONEY,
SIXTYONE MONEY,
NINETYPLUS MONEY
)

INSERT INTO #TP

(COMPANY,VENDORNUMBER,VENDORNAME,PARTIALPAYMENT)

SELECT COMPANY, VENDORNUMBER,VENDORNAME,SUM(PAYAMOUNT) AS PARTIALPAYMENT

FROM APOPEN

GROUP BY COMPANY,VENDORNUMBER,VENDORNAME

UPDATE #TP

SET CURRENTAMOUNT = DT.CURRENTAMOUNT FROM (SELECT SUM(AMOUNT) AS CURRENTAMOUNT, COMPANY, VENDORNUMBER,VENDORNAME
FROM APOPEN
WHERE DUEDATE > GETDATE()

GROUP BY COMPANY, VENDORNUMBER,VENDORNAME

)DT

WHERE #TP.COMPANY = DT.COMPANY AND #TP.VENDORNUMBER = DT.VENDORNUMBER

UPDATE #TP

SET ZEROTOTHIRTY = DT.ZEROTOTHIRTY FROM (SELECT SUM(AMOUNT) AS ZEROTOTHIRTY, COMPANY, VENDORNUMBER,VENDORNAME
FROM APOPEN
WHERE DATEDIFF(D,DUEDATE,GETDATE()) BETWEEN 0 AND 30

GROUP BY COMPANY, VENDORNUMBER,VENDORNAME

)DT

WHERE #TP.COMPANY = DT.COMPANY AND #TP.VENDORNUMBER = DT.VENDORNUMBER

UPDATE #TP

SET THIRTYTOSIXTY = DT.THIRTYTOSIXTY FROM (SELECT SUM(AMOUNT) AS THIRTYTOSIXTY, COMPANY, VENDORNUMBER,VENDORNAME
FROM APOPEN
WHERE DATEDIFF(D,DUEDATE,GETDATE()) BETWEEN 31 AND 60

GROUP BY COMPANY, VENDORNUMBER,VENDORNAME

)DT

WHERE #TP.COMPANY = DT.COMPANY AND #TP.VENDORNUMBER = DT.VENDORNUMBER

UPDATE #TP

SET NINETYPLUS = DT.NINETYPLUS FROM (SELECT SUM(AMOUNT) AS NINETYPLUS, COMPANY, VENDORNUMBER,VENDORNAME
FROM APOPEN
WHERE DATEDIFF(D,DUEDATE,GETDATE()) > 90

GROUP BY COMPANY, VENDORNUMBER,VENDORNAME

)DT

WHERE #TP.COMPANY = DT.COMPANY AND #TP.VENDORNUMBER = DT.VENDORNUMBER

SELECT * FROM #TP

DROP TABLE #TP

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 14:00:07

Did you take a look at what I mentioned yet?

Tara

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 14:04:28

I am not talking about the results. I am looking at the query itself, but I didn't know that existed, it will help for a text lookup function I wrote.

Brett it worked.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 14:08:46

What worked?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 14:10:34

Posting the query to this forum, it wrapped the lines.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 14:13:27

Oh you meant the query. We didn't realize that. I don't believe that there is a way to fix that in Query Analyzer. You would need to view it in Notepad with Word Wrap turned on. But even if you copy and paste it from Notepad into Query Analyzer, it isn't going to word wrap it for you because the line feed and carriage returns aren't there. You will need to manually add them in.

Tara

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-03 : 14:21:06

ooo yuck, that is what I was afraid of.

Thanks,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-03 : 19:05:45

This is very easy to do. Script out your procedures/views/queries to files and then open them in, or paste their contents into, Microsoft Word. Adjust the margins to suit your tastes, then choose Save As... (NOT Save) from the File menu. At the bottom of the screen is a drop down for file type, choose the one that says "MS-DOS text with layout" or "Text with line breaks" or whichever is closest to that description, then save the file. All of the soft line breaks will be converted to hard line breaks.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-10-04 : 07:35:33

Thanks for the info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources