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
 sub queries

Author  Topic 

stewart1127
Starting Member

8 Posts
Posted - 2008-11-25 : 09:25:34
I have a several SQL select statements that I need to combine into one querie with 2 sub queries

The primary select statement for the query is as follows:
SELECT DISTINCT "SCALE_CORP"."SCLC_NAME", "CSRV_ORDER_HDR"."ORDHD_ORDER", "CSRV_ORDER_HDR"."PK_ID", "CSRV_ORDER_HDR"."ORDHD_CORP", "CSRV_ORDER_LINE"."ORDLN_PLANT", "CSRV_ORDER_LINE"."ORDLN_LINE_DESC", "CSRV_ORDER_LINE"."ORDLN_MAJOR", "CSRV_ORDER_LINE"."ORDLN_MINOR", "CSRV_ORDER_HDR"."ORDHD_JOB_NAME", "CSRV_ORDER_HDR"."ORDHD_JOB_LOCATE", "CSRV_ORDER_HDR"."ORDHD_JOB_ROUTE", "CSRV_ORDER_LINE"."ORDLN_QTY", "CSRV_ORDER_LINE"."ORDLN_HAUL_PAY_UM", "CSRV_ADDRESSES"."CSRVADD_STREET2", "CSRV_ADDRESSES"."CSRVADD_NAME", "CSRV_ADDRESSES"."CSRVADD_STREET", "CSRV_ADDRESSES"."CSRVADD_CITY", "CSRV_ADDRESSES"."CSRVADD_STATE", "CSRV_ADDRESSES"."CSRVADD_ZIP", "CSRV_ORDER_HDR"."ORDHD_STOCK", "CSRV_ORDER_HDR"."ORDHD_ML_ADDRESS", "CSRV_ADDRESSES"."CSRVADD_CODE", "CSRV_ORDER_HDR"."ORDHD_SH_ADDRESS", "CSRV_ORDER_ADDRESS"."ORDADD_TYPE", "CSRV_ORDER_ADDRESS"."ORDADD_NAME", "CSRV_ORDER_ADDRESS"."ORDADD_STREET", "CSRV_ORDER_ADDRESS"."ORDADD_STREET2", "CSRV_ORDER_ADDRESS"."ORDADD_CITY", "CSRV_ORDER_ADDRESS"."ORDADD_STATE", "CSRV_ORDER_ADDRESS"."ORDADD_ZIP", "CSRV_ORDER_HDR"."ORDHD_JOB_CONTACT", "CSRV_ORDER_HDR"."ORDHD_JOB_DISTR", "CSRV_ORDER_HDR"."ORDHD_JOB_CMSID", "CSRV_ORDER_HDR"."ORDHD_PO_NUMBER", "CSRV_ORDER_HDR"."ORDHD_SALE_TYPE", "CSRV_ORDER_HDR"."ORDHD_COUNTY_SALE", "CSRV_ORDER_HDR"."ORDHD_STATE_SALE", "CSRV_ORDER_HDR"."ORDHD_TAXABLE", "CSRV_ORDER_LINE"."ORDLN_HAUL_TYPE", "CSRV_ORDER_LINE"."ORDLN_ZN_PRICE", "CSRV_ORDER_STAT"."ORDST_QTY_US", "CSRV_ORDER_STAT"."ORDST_TRIPS", "CSRV_ORDER_STAT"."ORDST_DELV_DATE", "CSRV_ORDER_LINE"."ORDLN_DELIV_DATE", "CSRV_ORDER_LINE"."ORDLN_DELIV_TIME", "CSRV_ORDER_HDR"."ORDHD_COSTING_JOB", "CSRV_ORDER_HDR"."ORDHD_CONTRACT_JOB"
FROM (((("DBADM"."CSRV_ORDER_HDR" "CSRV_ORDER_HDR" LEFT OUTER JOIN "DBADM"."CSRV_ORDER_LINE" "CSRV_ORDER_LINE" ON ("CSRV_ORDER_HDR"."PK_ID"="CSRV_ORDER_LINE"."FK_CSORDHD_ID") AND ("CSRV_ORDER_HDR"."ORDHD_CORP"="CSRV_ORDER_LINE"."ORDLN_CORP")) LEFT OUTER JOIN "DBADM"."CSRV_ADDRESSES" "CSRV_ADDRESSES" ON ((("CSRV_ORDER_HDR"."ORDHD_CORP"="CSRV_ADDRESSES"."CSRVADD_CORP") AND ("CSRV_ORDER_HDR"."ORDHD_CUSTOMER"="CSRV_ADDRESSES"."CSRVADD_CUSTOMER")) AND ("CSRV_ORDER_HDR"."ORDHD_SUFFIX"="CSRV_ADDRESSES"."CSRVADD_SUFFIX")) AND ("CSRV_ORDER_HDR"."ORDHD_COMPANY"="CSRV_ADDRESSES"."CSRVADD_COMPANY")) LEFT OUTER JOIN "DBADM"."CSRV_ORDER_ADDRESS" "CSRV_ORDER_ADDRESS" ON ("CSRV_ORDER_HDR"."PK_ID"="CSRV_ORDER_ADDRESS"."FK_CSORDHD_ID") AND ("CSRV_ORDER_HDR"."ORDHD_CORP"="CSRV_ORDER_ADDRESS"."ORDADD_CORP")) LEFT OUTER JOIN "DBADM"."SCALE_CORP" "SCALE_CORP" ON
("CSRV_ORDER_LINE"."ORDLN_CORP"="SCALE_CORP"."SCLC_CORP") AND ("CSRV_ORDER_LINE"."ORDLN_PLANT"="SCALE_CORP"."SCLC_PLANT")) LEFT OUTER JOIN "DBADM"."CSRV_ORDER_STAT" "CSRV_ORDER_STAT" ON ("CSRV_ORDER_LINE"."PK_ID"="CSRV_ORDER_STAT"."FK_CSORLINE_ID") AND ("CSRV_ORDER_LINE"."ORDLN_CORP"="CSRV_ORDER_STAT"."ORDST_CORP")
WHERE "CSRV_ORDER_HDR"."ORDHD_CORP"=90 AND "CSRV_ORDER_HDR"."ORDHD_ORDER"=811180001
ORDER BY "CSRV_ORDER_HDR"."ORDHD_CORP", "CSRV_ORDER_HDR"."ORDHD_ORDER", "CSRV_ORDER_LINE"."ORDLN_PLANT", "CSRV_ORDER_LINE"."ORDLN_LINE_DESC"



The sub query select statemets are: SELECT "CSRV_ORDER_PHONE"."ORDPHN_PHONE", "CSRV_ORDER_HDR"."PK_ID", "CSRV_ORDER_PHONE"."ORDPHN_TYPE"
FROM "DBADM"."CSRV_ORDER_HDR" "CSRV_ORDER_HDR" LEFT OUTER JOIN "DBADM"."CSRV_ORDER_PHONE" "CSRV_ORDER_PHONE" ON "CSRV_ORDER_HDR"."PK_ID"="CSRV_ORDER_PHONE"."FK_CSORDHD_ID"
WHERE "CSRV_ORDER_HDR"."PK_ID"=1298063

SELECT "CSRV_PHONES"."CSRVPHO_PHONE", "CSRV_ORDER_HDR"."PK_ID", "CSRV_PHONES"."CSRVPHO_PHONE_TYPE"
FROM "DBADM"."CSRV_ORDER_HDR" "CSRV_ORDER_HDR" INNER JOIN "DBADM"."CSRV_PHONES" "CSRV_PHONES" ON ((("CSRV_ORDER_HDR"."ORDHD_CORP"="CSRV_PHONES"."CSRVPHO_CORP") AND ("CSRV_ORDER_HDR"."ORDHD_CUSTOMER"="CSRV_PHONES"."CSRVPHO_CUSTOMER")) AND ("CSRV_ORDER_HDR"."ORDHD_SUFFIX"="CSRV_PHONES"."CSRVPHO_SUFFIX")) AND ("CSRV_ORDER_HDR"."ORDHD_COMPANY"="CSRV_PHONES"."CSRVPHO_COMPANY")
WHERE "CSRV_ORDER_HDR"."PK_ID"=1298063

SELECT "CSRV_ORDER_HDR"."PK_ID", "CSRV_ORD_DIRECT"."ORDIR_PLANT", "CSRV_ORD_DIRECT"."DIRECTIONS"
FROM "DBADM"."CSRV_ORDER_HDR" "CSRV_ORDER_HDR" INNER JOIN "DBADM"."CSRV_ORD_DIRECT" "CSRV_ORD_DIRECT" ON "CSRV_ORDER_HDR"."PK_ID"="CSRV_ORD_DIRECT"."FK_CSORHEAD_ID"
WHERE "CSRV_ORDER_HDR"."PK_ID"=1298063
ORDER BY "CSRV_ORD_DIRECT"."ORDIR_PLANT"
SELECT DISTINCT "CSRV_ORDER_HDR"."PK_ID", "CSRV_ORDER_LINE"."ORDLN_PLANT", "CSRV_ORD_NOTE"."ORNOTE_NOTE"
FROM ("DBADM"."CSRV_ORDER_HDR" "CSRV_ORDER_HDR" LEFT OUTER JOIN "DBADM"."CSRV_ORDER_LINE" "CSRV_ORDER_LINE" ON ("CSRV_ORDER_HDR"."ORDHD_CORP"="CSRV_ORDER_LINE"."ORDLN_CORP") AND ("CSRV_ORDER_HDR"."PK_ID"="CSRV_ORDER_LINE"."FK_CSORDHD_ID")) LEFT OUTER JOIN "DBADM"."CSRV_ORD_NOTE" "CSRV_ORD_NOTE" ON ("CSRV_ORDER_LINE"."ORDLN_CORP"="CSRV_ORD_NOTE"."ORNOTE_CORP") AND ("CSRV_ORDER_LINE"."PK_ID"="CSRV_ORD_NOTE"."FK_TABLE_ID")
WHERE "CSRV_ORDER_HDR"."PK_ID"=1298063
ORDER BY "CSRV_ORDER_LINE"."ORDLN_PLANT"

I have no idea where to start....

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-25 : 09:30:38
Start with aliasing you tables and use table alias with column names.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

stewart1127
Starting Member

8 Posts
Posted - 2008-11-25 : 11:23:53
Ok, so for instance
"csrv_order_hdr.ordhd_order" "addressid", etc?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-25 : 11:27:28
why are you wrapping columnnames/aliases around ""?
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-25 : 11:32:02
yep you can get rid of the the "" marks not requirement for them ' ' at the most or you can do [ ] if you are going to split the identifiying name but that really not good practice
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-25 : 11:32:20
When Crystal Reports generates a SQL statement it adds the double quotes. He may be taking the SQL out of Crystal.
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2008-11-25 : 11:34:36
Not sure but I think MS Access does the same if you change from the design mode over to SQL mode
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-25 : 11:38:17
Anyway it reads messes up the entire code making it really awful for somebody to review
Go to Top of Page

stewart1127
Starting Member

8 Posts
Posted - 2008-11-25 : 11:49:53
I did pull the sql out of crystal, and that might be why im having such issues with all the ' and "...


SELECT DISTINCT SCALE_CORP.SCLC_NAME, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC, CSRV_ORDER_LINE.ORDLN_MAJOR, CSRV_ORDER_LINE.ORDLN_MINOR, CSRV_ORDER_HDR.ORDHD_JOB_NAME, CSRV_ORDER_HDR.ORDHD_JOB_LOCATE, CSRV_ORDER_HDR.ORDHD_JOB_ROUTE, CSRV_ORDER_LINE.ORDLN_QTY, CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM, CSRV_ADDRESSES.CSRVADD_STREET2, CSRV_ADDRESSES.CSRVADD_NAME, CSRV_ADDRESSES.CSRVADD_STREET, CSRV_ADDRESSES.CSRVADD_CITY, CSRV_ADDRESSES.CSRVADD_STATE, CSRV_ADDRESSES.CSRVADD_ZIP, CSRV_ORDER_HDR.ORDHD_STOCK, CSRV_ORDER_HDR.ORDHD_ML_ADDRESS, CSRV_ADDRESSES.CSRVADD_CODE, CSRV_ORDER_HDR.ORDHD_SH_ADDRESS, CSRV_ORDER_ADDRESS.ORDADD_TYPE, CSRV_ORDER_ADDRESS.ORDADD_NAME, CSRV_ORDER_ADDRESS.ORDADD_STREET, CSRV_ORDER_ADDRESS.ORDADD_STREET2, CSRV_ORDER_ADDRESS.ORDADD_CITY, CSRV_ORDER_ADDRESS.ORDADD_STATE, CSRV_ORDER_ADDRESS.ORDADD_ZIP, CSRV_ORDER_HDR.ORDHD_JOB_CONTACT, CSRV_ORDER_HDR.ORDHD_JOB_DISTR, CSRV_ORDER_HDR.ORDHD_JOB_CMSID, CSRV_ORDER_HDR.ORDHD_PO_NUMBER, CSRV_ORDER_HDR.ORDHD_SALE_TYPE, CSRV_ORDER_HDR.ORDHD_COUNTY_SALE, CSRV_ORDER_HDR.ORDHD_STATE_SALE, CSRV_ORDER_HDR.ORDHD_TAXABLE, CSRV_ORDER_LINE.ORDLN_HAUL_TYPE, CSRV_ORDER_LINE.ORDLN_ZN_PRICE, CSRV_ORDER_STAT.ORDST_QTY_US, CSRV_ORDER_STAT.ORDST_TRIPS, CSRV_ORDER_STAT.ORDST_DELV_DATE, CSRV_ORDER_LINE.ORDLN_DELIV_DATE, CSRV_ORDER_LINE.ORDLN_DELIV_TIME, CSRV_ORDER_HDR.ORDHD_COSTING_JOB, CSRV_ORDER_HDR.ORDHD_CONTRACT_JOB
FROM ((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR LEFT OUTER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP)) LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ADDRESSES.CSRVADD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_ADDRESSES.CSRVADD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_ADDRESSES.CSRVADD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_ADDRESSES.CSRVADD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_ORDER_ADDRESS CSRV_ORDER_ADDRESS ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_ADDRESS.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_ADDRESS.ORDADD_CORP)) LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP ON
(CSRV_ORDER_LINE.ORDLN_CORP=SCALE_CORP.SCLC_CORP) AND (CSRV_ORDER_LINE.ORDLN_PLANT=SCALE_CORP.SCLC_PLANT)) LEFT OUTER JOIN DBADM.CSRV_ORDER_STAT CSRV_ORDER_STAT ON (CSRV_ORDER_LINE.PK_ID=CSRV_ORDER_STAT.FK_CSORLINE_ID) AND (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORDER_STAT.ORDST_CORP)
WHERE CSRV_ORDER_HDR.ORDHD_CORP=90 AND CSRV_ORDER_HDR.ORDHD_ORDER=811180001
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC

Order Phone Sub-report

SELECT CSRV_ORDER_PHONE.ORDPHN_PHONE, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_PHONE.ORDPHN_TYPE
FROM DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR LEFT OUTER JOIN DBADM.CSRV_ORDER_PHONE CSRV_ORDER_PHONE ON CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_PHONE.FK_CSORDHD_ID
WHERE CSRV_ORDER_HDR.PK_ID=1298063


Customer Phone Sub-report


SELECT CSRV_PHONES.CSRVPHO_PHONE, CSRV_ORDER_HDR.PK_ID, CSRV_PHONES.CSRVPHO_PHONE_TYPE
FROM DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR INNER JOIN DBADM.CSRV_PHONES CSRV_PHONES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_PHONES.CSRVPHO_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_PHONES.CSRVPHO_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_PHONES.CSRVPHO_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_PHONES.CSRVPHO_COMPANY)
WHERE CSRV_ORDER_HDR.PK_ID=1298063


Shipping Notes Sub-report


SELECT DISTINCT CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORD_NOTE.ORNOTE_NOTE
FROM (DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR LEFT OUTER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP) AND (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID)) LEFT OUTER JOIN DBADM.CSRV_ORD_NOTE CSRV_ORD_NOTE ON (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORD_NOTE.ORNOTE_CORP) AND (CSRV_ORDER_LINE.PK_ID=CSRV_ORD_NOTE.FK_TABLE_ID)
WHERE CSRV_ORDER_HDR.PK_ID=1298063
ORDER BY CSRV_ORDER_LINE.ORDLN_PLANT


Im so new, im not even sure what you mean by aliasing
Go to Top of Page

stewart1127
Starting Member

8 Posts
Posted - 2008-11-25 : 11:54:20
so if im understanding you, i need to do something like
declare sqladdress varchar (max)
set sqladdress = select SCALE_CORP.SCLC_NAME customername, CSRV_ORDER_HDR.ORDHD_ORDER ordernumber, etc?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-25 : 11:55:25
Stewart, just a suggestion. First, if you want people to look at your queries, format them properly and put code tags around them like this: 

SELECT   DISTINCT SCALE_CORP.SCLC_NAME,
                  CSRV_ORDER_HDR.ORDHD_ORDER,
                  CSRV_ORDER_HDR.PK_ID,
                  CSRV_ORDER_HDR.ORDHD_CORP,
                  CSRV_ORDER_LINE.ORDLN_PLANT,
                  CSRV_ORDER_LINE.ORDLN_LINE_DESC,
                  CSRV_ORDER_LINE.ORDLN_MAJOR,
                  CSRV_ORDER_LINE.ORDLN_MINOR,
                  CSRV_ORDER_HDR.ORDHD_JOB_NAME,
                  CSRV_ORDER_HDR.ORDHD_JOB_LOCATE,
                  CSRV_ORDER_HDR.ORDHD_JOB_ROUTE,
                  CSRV_ORDER_LINE.ORDLN_QTY,
                  CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM,
                  CSRV_ADDRESSES.CSRVADD_STREET2,
                  CSRV_ADDRESSES.CSRVADD_NAME,
                  CSRV_ADDRESSES.CSRVADD_STREET,
                  CSRV_ADDRESSES.CSRVADD_CITY,
                  CSRV_ADDRESSES.CSRVADD_STATE,
                  CSRV_ADDRESSES.CSRVADD_ZIP,
                  CSRV_ORDER_HDR.ORDHD_STOCK,
                  CSRV_ORDER_HDR.ORDHD_ML_ADDRESS,
                  CSRV_ADDRESSES.CSRVADD_CODE,
                  CSRV_ORDER_HDR.ORDHD_SH_ADDRESS,
                  CSRV_ORDER_ADDRESS.ORDADD_TYPE,
                  CSRV_ORDER_ADDRESS.ORDADD_NAME,
                  CSRV_ORDER_ADDRESS.ORDADD_STREET,
                  CSRV_ORDER_ADDRESS.ORDADD_STREET2,
                  CSRV_ORDER_ADDRESS.ORDADD_CITY,
                  CSRV_ORDER_ADDRESS.ORDADD_STATE,
                  CSRV_ORDER_ADDRESS.ORDADD_ZIP,
                  CSRV_ORDER_HDR.ORDHD_JOB_CONTACT,
                  CSRV_ORDER_HDR.ORDHD_JOB_DISTR,
                  CSRV_ORDER_HDR.ORDHD_JOB_CMSID,
                  CSRV_ORDER_HDR.ORDHD_PO_NUMBER,
                  CSRV_ORDER_HDR.ORDHD_SALE_TYPE,
                  CSRV_ORDER_HDR.ORDHD_COUNTY_SALE,
                  CSRV_ORDER_HDR.ORDHD_STATE_SALE,
                  CSRV_ORDER_HDR.ORDHD_TAXABLE,
                  CSRV_ORDER_LINE.ORDLN_HAUL_TYPE,
                  CSRV_ORDER_LINE.ORDLN_ZN_PRICE,
                  CSRV_ORDER_STAT.ORDST_QTY_US,
                  CSRV_ORDER_STAT.ORDST_TRIPS,
                  CSRV_ORDER_STAT.ORDST_DELV_DATE,
                  CSRV_ORDER_LINE.ORDLN_DELIV_DATE,
                  CSRV_ORDER_LINE.ORDLN_DELIV_TIME,
                  CSRV_ORDER_HDR.ORDHD_COSTING_JOB,
                  CSRV_ORDER_HDR.ORDHD_CONTRACT_JOB
FROM     ((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR
             LEFT OUTER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE
               ON (CSRV_ORDER_HDR.PK_ID = CSRV_ORDER_LINE.FK_CSORDHD_ID)
                  AND (CSRV_ORDER_HDR.ORDHD_CORP = CSRV_ORDER_LINE.ORDLN_CORP))
            LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES
              ON (((CSRV_ORDER_HDR.ORDHD_CORP = CSRV_ADDRESSES.CSRVADD_CORP)
                   AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER = CSRV_ADDRESSES.CSRVADD_CUSTOMER))
                  AND (CSRV_ORDER_HDR.ORDHD_SUFFIX = CSRV_ADDRESSES.CSRVADD_SUFFIX))
                 AND (CSRV_ORDER_HDR.ORDHD_COMPANY = CSRV_ADDRESSES.CSRVADD_COMPANY))
           LEFT OUTER JOIN DBADM.CSRV_ORDER_ADDRESS CSRV_ORDER_ADDRESS
             ON (CSRV_ORDER_HDR.PK_ID = CSRV_ORDER_ADDRESS.FK_CSORDHD_ID)
                AND (CSRV_ORDER_HDR.ORDHD_CORP = CSRV_ORDER_ADDRESS.ORDADD_CORP))
          LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP
            ON (CSRV_ORDER_LINE.ORDLN_CORP = SCALE_CORP.SCLC_CORP)
               AND (CSRV_ORDER_LINE.ORDLN_PLANT = SCALE_CORP.SCLC_PLANT))
         LEFT OUTER JOIN DBADM.CSRV_ORDER_STAT CSRV_ORDER_STAT
           ON (CSRV_ORDER_LINE.PK_ID = CSRV_ORDER_STAT.FK_CSORLINE_ID)
              AND (CSRV_ORDER_LINE.ORDLN_CORP = CSRV_ORDER_STAT.ORDST_CORP)
WHERE    CSRV_ORDER_HDR.ORDHD_CORP = 90
         AND CSRV_ORDER_HDR.ORDHD_ORDER = 811180001
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP,
         CSRV_ORDER_HDR.ORDHD_ORDER,
         CSRV_ORDER_LINE.ORDLN_PLANT,
         CSRV_ORDER_LINE.ORDLN_LINE_DESC



The code tags correspond to the # sign in the format toolbar when you post. You can use a free site like this to format your code so it's readable.

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
Go to Top of Page

stewart1127
Starting Member

8 Posts
Posted - 2008-11-25 : 13:00:26
That is much easier to read
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site