SELECT AS, NOT WORKING WITH UNION

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

SELECT AS, NOT WORKING WITH UNION

Author

Topic

Subprime Accountant
Starting Member

3 Posts

Posted - 2009-02-02 : 10:50:44

Greetings,

Using Microsoft Query 2002. The following query pulls A/P information for specific GL's from both historical and current tables. That part works correctly. I Rtrim the P.O. Numbers to get rid of junk and return only values. The Trim works correct

RIGHT(PM20000.PORDNMBR,4)AS 'P.O.'

but everytime I try to name the column as 'P.O.' MSQuery just deletes the AS clause from my script and returns and unnamed column.

The following is the script -

SELECT

PM20000.DOCTYPE,
PM20000.VENDORID,
PM20000.DOCDATE,
PM20000.DOCAMNT,
GL00105.ACTNUMST,
GL00100.ACTDESCR,
RIGHT(PM20000.PORDNMBR,4)AS 'P.O.'

FROM CRW.dbo.PM20000 PM20000

LEFT OUTER JOIN CRW.dbo.PM10100 PM10100
ON PM20000.VCHRNMBR = PM10100.VCHRNMBR

LEFT OUTER JOIN CRW.dbo.GL00100 ON PM10100.DSTINDX = CRW.dbo.GL00100.ACTINDX

LEFT OUTER JOIN CRW.dbo.GL00105 ON PM10100.DSTINDX = CRW.dbo.GL00105.ACTINDX

WHERE
(GL00105.ACTNUMST='10-12270-00-00000') OR
(GL00105.ACTNUMST='10-12271-00-00000') OR
(GL00105.ACTNUMST='10-12271-00-00001') OR
(GL00105.ACTNUMST='10-12273-00-00000')

UNION

SELECT

PM30200.DOCTYPE,
PM30200.VENDORID,
PM30200.DOCDATE,
PM30200.DOCAMNT,
GL00105.ACTNUMST,
GL00100.ACTDESCR,
RIGHT(PM30200.PORDNMBR,4) AS 'P.O.'

FROM CRW.dbo.PM30200 PM30200

LEFT OUTER JOIN CRW.dbo.PM30600 PM30600
ON PM30200.VCHRNMBR = PM30600.VCHRNMBR

LEFT OUTER JOIN CRW.dbo.GL00100 ON PM30600.DSTINDX = CRW.dbo.GL00100.ACTINDX

LEFT OUTER JOIN CRW.dbo.GL00105 ON PM30600.DSTINDX = CRW.dbo.GL00105.ACTINDX

WHERE
(GL00105.ACTNUMST='10-12270-00-00000') OR
(GL00105.ACTNUMST='10-12271-00-00000') OR
(GL00105.ACTNUMST='10-12271-00-00001') OR
(GL00105.ACTNUMST='10-12273-00-00000')

Here is what the query returns

ACTNUMST ACTDESCR
10-12273-00-00000 Inventory-Parts-CRW 5627
10-12273-00-00000 Inventory-Parts-CRW 5630
10-12273-00-00000 Inventory-Parts-CRW 5629
10-12273-00-00000 Inventory-Parts-CRW

Any advice would be greatly appreciated.

Your Friend, And Mine...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 10:54:13

try RIGHT(PM20000.PORDNMBR,4) AS [P.O.] instead.
b/w whats Microsoft Query 2002?

Subprime Accountant
Starting Member

3 Posts

Posted - 2009-02-02 : 12:26:02

I thank you for your suggestion, however, it has proven unsuccessful.

To answer your question: I am querying using the SQL tool imbebbed in Excel: Data, Import Externa Data, Edit Query... and you end up in with a graphical query tool.

Your Friend, And Mine...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-02 : 12:36:47

try this,

[P.O.]=RIGHT(PM20000.PORDNMBR,4)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:39:44

i think it may be this

http://support.microsoft.com/kb/298955

Subprime Accountant
Starting Member

3 Posts

Posted - 2009-02-02 : 13:45:24

My system was up to date on Service Packs.

I ran the hot fix, to no avail.

Modifying the query to

SELECT
PM20000.DOCTYPE,
PM20000.VENDORID,
PM20000.DOCDATE,
PM20000.DOCAMNT,
RIGHT(PM20000.PORDNMBR,4) AS PO
FROM CRW.dbo.PM20000 PM20000

This correctly renames my column. My guess is somehow the joins limit the functionality, as I've tried the query from the union up and it would not rename the column while including any of the GL tables.

Your Friend, And Mine...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2009-02-02 : 15:31:25

Create a view in SQL Server, defined however you want and returning any columns/expressions that you want with the labels you need, and then simply query the View from MS Query.

This will be much easier to maintain anyway going forward, and probably more flexible and more efficient as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources