Select a field, but not have it show up in output? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Select a field, but not have it show up in output?

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-31 : 13:30:35

Hi All,

I have a query where I need to replicate a field in one select statement, and then union it to another select statement with an order by at the end. Because of the replicate, I don't have the field in the first query that is in the order by. SQL wants that field in the first query to do the order by.

Is there any way to select a field, but not have it show up in the display?

Here are the selects (the issue being that when the status is 4, they want the Assigned To column to actually be the data from the Create_USR.Name field)

If it is any other status, they want the AssignedTo data to come from the real Assigned to field.

1st select

SELECT
replicate(CREATE_USR.NAME,1) AS AssignedTo,
ASSIGNED_USR.NAME AssignedTo,
INV.INV_NO InvoiceNo,
VEND_REMIT.NAME RemitVendorName,
convert(char,INV.INV_DATE,1) InvDate,
convert(char,INV.STAT_CHG_DATE,1) LastProcessedDate,
convert(char,INV.DUE_DATE,1) DueDate,
PO.PO_NO PONo,

CASE when INV.STAT = '4' then '4 - Approved'
else 'other'
END INV_STAT,

CREATE_USR.NAME LastProcessedBy,
APPR_USR.NAME ComposedBy,
INV.GROSS_INV_AMT InvoiceTotal,
TodaysDate=convert(varchar, GETDATE(), 101),
DATEDIFF(day, getdate(), DUE_DATE) AS DOS

(all the froms and joins -- yada yada and a union)

2nd Select

SELECT
ASSIGNED_USR.NAME AssignedTo,
INV.INV_NO InvoiceNo,
VEND_REMIT.NAME RemitVendorName,
convert(char,INV.INV_DATE,1) InvDate,
convert(char,INV.STAT_CHG_DATE,1) LastProcessedDate,
convert(char,INV.DUE_DATE,1) DueDate,
PO.PO_NO PONo,

CASE when INV.STAT = '3' then '3 - Discrepant'
when INV.STAT = '5' then '5 - DraftDiscrepant'
when INV.STAT = '6' then '6 - DraftApproved'
when INV.STAT = '17' then '7 - DiscrPendRecpts'
else 'other'
END INV_STAT,

CREATE_USR.NAME LastProcessedBy,
APPR_USR.NAME ComposedBy,
INV.GROSS_INV_AMT InvoiceTotal,
TodaysDate=convert(varchar, GETDATE(), 101),
DATEDIFF(day, getdate(), DUE_DATE) AS DOS

order by
ASSIGNED_USR.NAME,
VEND_REMIT.NAME ,
INV.INV_NO

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-31 : 15:07:27

Sherrie...
You have AssignedTo defined in your first select statement twice.
Then you are trying to order by ASSIGNED_USR.NAME, but you have aliased it in both statements.
Fix this please, before we look any further at resolving your issue.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-31 : 15:23:22

Yes, I know. Sorry, I was dinking around trying to get things to work.

If I take that out, it won't work **if** my 'order by' looks like this:

order by
ASSIGNED_USR.NAME,
VEND_REMIT.NAME ,
INV.INV_NO

because I get this error message:

Msg 104, Level 15, State 1, Line 7
ORDER BY items must appear in the select list if the statement contains a UNION operator.

If I make the 'order by' look like this (with Assigned User taken out) it works:

order by
VEND_REMIT.NAME ,
INV.INV_NO

The whole problem being is that the users WANT the first sort criteria to be the ASSIGNED_USR.NAME.

Thanks - and sorry for the confusion.

Sherri

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-31 : 15:27:19

If it helps, here is the entire code. I have assigned user commented out in the order by clause below - this does run and works as it stands, but I need to be able to do the order by the assigned user.

SELECT

replicate(CREATE_USR.NAME,1) AS AssignedTo,
INV.INV_NO InvoiceNo,
VEND_REMIT.NAME RemitVendorName,
convert(char,INV.INV_DATE,1) InvDate,
convert(char,INV.STAT_CHG_DATE,1) LastProcessedDate,
convert(char,INV.DUE_DATE,1) DueDate,
PO.PO_NO PONo,

CASE when INV.STAT = '4' then '4 - Approved'
else 'other'
END INV_STAT,

CREATE_USR.NAME LastProcessedBy,
APPR_USR.NAME ComposedBy,
INV.GROSS_INV_AMT InvoiceTotal,
TodaysDate=convert(varchar, GETDATE(), 101),
DATEDIFF(day, getdate(), DUE_DATE) AS DOS

from INV join PO
on( PO.PO_ID=INV.PO_ID and
PO.PO_IDB=INV.PO_IDB )

join VEND_REMIT
on( INV.VEND_REMIT_ID=VEND_REMIT.VEND_REMIT_ID and
INV.VEND_REMIT_ID=VEND_REMIT.VEND_REMIT_ID )

join USR CREATE_USR
on( INV.REC_CREATE_USR_ID = CREATE_USR.USR_ID and
INV.REC_CREATE_USR_IDB = CREATE_USR.USR_IDB)

join USR APPR_USR
on( INV.APPR_USR_ID= APPR_USR.USR_ID and
INV.APPR_USR_IDB= APPR_USR.USR_IDB )

join USR ASSIGNED_USR
on( INV.ASSIGNED_USR_ID = ASSIGNED_USR.USR_ID and
INV.ASSIGNED_USR_IDB = ASSIGNED_USR.USR_IDB)

where INV.STAT = 4

union

SELECT
ASSIGNED_USR.NAME AssignedTo,
INV.INV_NO InvoiceNo,
VEND_REMIT.NAME RemitVendorName,
convert(char,INV.INV_DATE,1) InvDate,
convert(char,INV.STAT_CHG_DATE,1) LastProcessedDate,
convert(char,INV.DUE_DATE,1) DueDate,
PO.PO_NO PONo,

CASE when INV.STAT = '3' then '3 - Discrepant'
when INV.STAT = '5' then '5 - DraftDiscrepant'
when INV.STAT = '6' then '6 - DraftApproved'
when INV.STAT = '17' then '7 - DiscrPendRecpts'
else 'other'
END INV_STAT,

CREATE_USR.NAME LastProcessedBy,
APPR_USR.NAME ComposedBy,
INV.GROSS_INV_AMT InvoiceTotal,
TodaysDate=convert(varchar, GETDATE(), 101),
DATEDIFF(day, getdate(), DUE_DATE) AS DOS

from INV join PO
on( PO.PO_ID=INV.PO_ID and
PO.PO_IDB=INV.PO_IDB )

join VEND_REMIT
on( INV.VEND_REMIT_ID=VEND_REMIT.VEND_REMIT_ID and
INV.VEND_REMIT_ID=VEND_REMIT.VEND_REMIT_ID )

join USR CREATE_USR
on( INV.REC_CREATE_USR_ID = CREATE_USR.USR_ID and
INV.REC_CREATE_USR_IDB = CREATE_USR.USR_IDB)

join USR APPR_USR
on( INV.APPR_USR_ID= APPR_USR.USR_ID and
INV.APPR_USR_IDB= APPR_USR.USR_IDB )

join USR ASSIGNED_USR
on( INV.ASSIGNED_USR_ID = ASSIGNED_USR.USR_ID and
INV.ASSIGNED_USR_IDB = ASSIGNED_USR.USR_IDB)

where INV.STAT IN (3,5,6,17)

order by
-- ASSIGNED_USR.NAME,
VEND_REMIT.NAME ,
INV.INV_NO

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-01 : 11:17:49

First, you should be able to order by the alias name you have assigned, rather than the actual column name.
Second, avoid the use of superfluous parenthesis. Use them only when the order of operations is unclear. For a string of "and" clauses, they are pointless. Effective use of whitespace is better way to achieve clarity in your code.
Does the following code yield an error?

SELECT	replicate(CREATE_USR.NAME,1) AS AssignedTo,
	INV.INV_NO InvoiceNo,
	VEND_REMIT.NAME RemitVendorName,
	convert(char, INV.INV_DATE, 1) InvDate,
	convert(char, INV.STAT_CHG_DATE, 1) LastProcessedDate,
	convert(char, INV.DUE_DATE, 1) DueDate,
	PO.PO_NO PONo,
	CASE	when INV.STAT = '4' then '4 - Approved'
		else 'other'
		END INV_STAT,
	CREATE_USR.NAME LastProcessedBy,
	APPR_USR.NAME ComposedBy,
	INV.GROSS_INV_AMT InvoiceTotal,
	TodaysDate = convert(varchar, GETDATE(), 101),
	DATEDIFF(day, getdate(), DUE_DATE) AS DOS
from	INV
	inner join PO
		on PO.PO_ID=INV.PO_ID
		and PO.PO_IDB=INV.PO_IDB
	inner join VEND_REMIT
		on INV.VEND_REMIT_ID = VEND_REMIT.VEND_REMIT_ID
		and INV.VEND_REMIT_ID = VEND_REMIT.VEND_REMIT_ID
	inner join USR CREATE_USR
		on INV.REC_CREATE_USR_ID = CREATE_USR.USR_ID
		and INV.REC_CREATE_USR_IDB = CREATE_USR.USR_IDB
	inner join USR APPR_USR
		on INV.APPR_USR_ID = APPR_USR.USR_ID
		and INV.APPR_USR_IDB= APPR_USR.USR_IDB
	inner join USR ASSIGNED_USR
		on INV.ASSIGNED_USR_ID = ASSIGNED_USR.USR_ID
		and INV.ASSIGNED_USR_IDB = ASSIGNED_USR.USR_IDB
where	INV.STAT = 4
union
SELECT	ASSIGNED_USR.NAME AssignedTo,
	INV.INV_NO InvoiceNo,
	VEND_REMIT.NAME RemitVendorName,
	convert(char,INV.INV_DATE,1) InvDate,
	convert(char,INV.STAT_CHG_DATE,1) LastProcessedDate,
	convert(char,INV.DUE_DATE,1) DueDate,
	PO.PO_NO PONo,
	CASE	when INV.STAT = '3' then '3 - Discrepant'
		when INV.STAT = '5' then '5 - DraftDiscrepant'
		when INV.STAT = '6' then '6 - DraftApproved'
		when INV.STAT = '17' then '7 - DiscrPendRecpts'
		else 'other'
		END INV_STAT,
	CREATE_USR.NAME LastProcessedBy,
	APPR_USR.NAME ComposedBy,
	INV.GROSS_INV_AMT InvoiceTotal,
	TodaysDate = convert(varchar, GETDATE(), 101),
	DATEDIFF(day, getdate(), DUE_DATE) AS DOS
from	INV
	inner join PO
		on PO.PO_ID = INV.PO_ID
		and PO.PO_IDB = INV.PO_IDB
	inner join VEND_REMIT
		on INV.VEND_REMIT_ID = VEND_REMIT.VEND_REMIT_ID
		and INV.VEND_REMIT_ID = VEND_REMIT.VEND_REMIT_ID
	inner join USR CREATE_USR
		on INV.REC_CREATE_USR_ID = CREATE_USR.USR_ID
		and INV.REC_CREATE_USR_IDB = CREATE_USR.USR_IDB
	inner join USR APPR_USR
		on INV.APPR_USR_ID = APPR_USR.USR_ID
		and INV.APPR_USR_IDB = APPR_USR.USR_IDB
	inner join USR ASSIGNED_USR
		on INV.ASSIGNED_USR_ID = ASSIGNED_USR.USR_ID
		and INV.ASSIGNED_USR_IDB = ASSIGNED_USR.USR_IDB
where	INV.STAT IN (3,5,6,17)
order by AssignedTo,
	VEND_REMIT.NAME ,
	INV.INV_NO

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-01-02 : 13:04:38

Hello Blindman.

Thank you for all of your comments. My code was formatted similar to what you had, but when I dumped it here, the formatting changed. I couldn't make it indent like you had it. So I separated it to hopefully make it a little easier to read... however, there was still a lot that you had to say that was very helpful, and I do appreciate that. The code you provided did work! I didn't know that you could sort by the Alias Name. That is a very cool thing to learn.

Thanks again Blindman - and everyone else who has helped me me with this.

Regards,
Sherri Reid

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-05 : 09:48:12

You're welcome Sherrie.
If you want to retain formatting when you post code, include it in "[code]...[/c0de]" brackets.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources