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)
 Field is not displaying

Author  Topic 

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-05-16 : 14:51:12
Hi again everyone,

Yes I am making alot of post today :).

Now here is my dilemma.

I would like to display the column called 'RoundedTax' in my output. Everything else is displaying but this field is not coming up.

Background :- A table called SCLData_tab contains the raw data that this query is pulling from. This table has the headings of number e.g F254 OR F1068

Below are he real fields names of the SCLData_tab table along with the names i am assigning to them 


F1068 AS TransactionType,
F254 AS AccountingDate,
F253 AS SystemDate,
F1056 AS StoreId,
F1057 AS LaneNumber,
F1035 AS TransactionStartTime,
F1036 AS TransactionEndTime,
F1032 AS TransactionNumber ,
F1764 AS InvoiceNumber,
F1185 AS OperatorNumber,
F1126 AS OperatorCode,
F1127 AS OperatorName,
F1148 AS MagnaNumberShort,
F01 AS ProductBarcode,
F02 AS ProductDescription, 
F04 AS SubDepartmentCode,
F03 AS DepartmentCode,
F97 AS TaxableFlag,
F113 AS ActivePriceDescription,
F1101 AS ItemLineNumber,
F1007 AS ActivePrice,
F1006 AS ActiveQuantity,
F65 AS DollarAmount,
F64 AS Quantity, 
F1263 AS RoundedTax,
F238 AS DepartmentDescription,
F1022 AS SubDeptDescription,
F9999 AS XmlFile



The code in green represents the query that I am working with



/*
--Insert into Results
SELECT 	SCLData_Unique_Sale_Summary.*,
	SCLData_Unique_Product_Summary.ProductBarcode,
	SCLData_Unique_Product_Summary.ProductDescription,
	SCLData_Unique_Product_Summary.ActivePrice,
	SCLData_Unique_Product_Summary.TotalProductQuantity,
	SCLData_Unique_Product_Summary.TotalProductSalePrice,
	SCLData_Unique_Product_Summary.DepartmentDescription,
	SCLData_Unique_Product_Summary.SubDeptDescription,
	CASE DATEPART( dw, AccountingDate )
		WHEN '1' THEN 'SUNDAY'
		WHEN '2' THEN 'MONDAY'
		WHEN '3' THEN 'TUESDAY'
		WHEN '4' THEN 'WEDNESDAY'
		WHEN '5' THEN 'THURSDAY'
		WHEN '6' THEN 'FRIDAY'
		WHEN '7' THEN 'SATURDAY'
	END AS AccountDateWeekDay,
	CASE DATEPART( m, AccountingDate )
		WHEN '1' THEN 'JAN'
		WHEN '2' THEN 'FEB'
		WHEN '3' THEN 'MAR'
		WHEN '4' THEN 'APR'
		WHEN '5' THEN 'MAY'
		WHEN '6' THEN 'JUN'
		WHEN '7' THEN 'JUL'
		WHEN '8' THEN 'AUG'
		WHEN '9' THEN 'SEP'
		WHEN '10' THEN 'OCT'
		WHEN '11' THEN 'NOV'
		WHEN '12' THEN 'DEC'
	END AS AccountDateMonth,
	CASE SCLData_Unique_Sale_Summary.StoreId
		WHEN '105' THEN '3001'
		WHEN '101' THEN '3024'
		WHEN '112' THEN '3025'
		WHEN '103' THEN '3003'
		WHEN '102' THEN '3002'
	END AS SubsidiaryId
FROM	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 
			ProductBarcode,
			MAX( ProductDescription ) ProductDescription,
			MAX( ActivePrice ) ActivePrice,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalProductQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalProductSalePrice,
			MAX( DepartmentDescription ) DepartmentDescription,
			MAX( SubDeptDescription ) SubDeptDescription
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, TransactionNumber, ProductBarcode
	) AS SCLData_Unique_Product_Summary,
	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 
			MAX( MagnaNumberShort ) MagnaNumberShort,
			MAX( TransactionType ) TransactionType,
			MIN( AccountingDate ) AccountingDate,
			MIN( TransactionStartTime ) TransactionStartTime,
			MAX( TransactionEndTime ) TransactionEndTime,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalSaleQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalSalePrice
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, TransactionNumber
	) AS SCLData_Unique_Sale_Summary
	WHERE SCLData_Unique_Sale_Summary.StoreId = SCLData_Unique_Product_Summary.StoreId
	AND SCLData_Unique_Sale_Summary.LaneNumber = SCLData_Unique_Product_Summary.LaneNumber
	AND SCLData_Unique_Sale_Summary.InvoiceNumber = SCLData_Unique_Product_Summary.InvoiceNumber
	AND SCLData_Unique_Sale_Summary.TransactionNumber = SCLData_Unique_Product_Summary.TransactionNumber



Basically I am doing some formatting along with some small calculations.

Any help is greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-16 : 14:59:54
RoundedTax isn't displaying because you've only added it to the derived tables. You need to also include it in the column list of the SELECT that does the output. Add it before the first FROM in the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-05-16 : 15:05:59
I add it to the first select statement and it still gives the error Invalid Column Name.


SELECT 	TOP 2 SCLData_Unique_Sale_Summary.*,
	SCLData_Unique_Product_Summary.ProductBarcode,
	SCLData_Unique_Product_Summary.ProductDescription,
	SCLData_Unique_Product_Summary.ActivePrice,
	SCLData_Unique_Product_Summary.TotalProductQuantity,
	SCLData_Unique_Product_Summary.TotalProductSalePrice,
	SCLData_Unique_Product_Summary.DepartmentDescription,
	SCLData_Unique_Product_Summary.SubDeptDescription,
	SCLData_Unique_Product_Summary.RoundedTax,
	CASE DATEPART( dw, AccountingDate )
		WHEN '1' THEN 'SUNDAY'
		WHEN '2' THEN 'MONDAY'
		WHEN '3' THEN 'TUESDAY'
		WHEN '4' THEN 'WEDNESDAY'
		WHEN '5' THEN 'THURSDAY'
		WHEN '6' THEN 'FRIDAY'
		WHEN '7' THEN 'SATURDAY'
	END AS AccountDateWeekDay,
	CASE DATEPART( m, AccountingDate )
		WHEN '1' THEN 'JAN'
		WHEN '2' THEN 'FEB'
		WHEN '3' THEN 'MAR'
		WHEN '4' THEN 'APR'
		WHEN '5' THEN 'MAY'
		WHEN '6' THEN 'JUN'
		WHEN '7' THEN 'JUL'
		WHEN '8' THEN 'AUG'
		WHEN '9' THEN 'SEP'
		WHEN '10' THEN 'OCT'
		WHEN '11' THEN 'NOV'
		WHEN '12' THEN 'DEC'
	END AS AccountDateMonth,
	CASE SCLData_Unique_Sale_Summary.StoreId
		WHEN '105' THEN '3001'
		WHEN '101' THEN '3024'
		WHEN '112' THEN '3025'
		WHEN '103' THEN '3003'
		WHEN '102' THEN '3002'
	END AS SubsidiaryId
FROM	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 
			ProductBarcode,
			MAX( ProductDescription ) ProductDescription,
			MAX( ActivePrice ) ActivePrice,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalProductQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalProductSalePrice,
			MAX( DepartmentDescription ) DepartmentDescription,
			MAX( SubDeptDescription ) SubDeptDescription
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, TransactionNumber, ProductBarcode
	) AS SCLData_Unique_Product_Summary,
	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 
			MAX( MagnaNumberShort ) MagnaNumberShort,
			MAX( TransactionType ) TransactionType,
			MIN( AccountingDate ) AccountingDate,
			MIN( TransactionStartTime ) TransactionStartTime,
			MAX( TransactionEndTime ) TransactionEndTime,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalSaleQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalSalePrice
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, TransactionNumber
	) AS SCLData_Unique_Sale_Summary
	WHERE SCLData_Unique_Sale_Summary.StoreId = SCLData_Unique_Product_Summary.StoreId
	AND SCLData_Unique_Sale_Summary.LaneNumber = SCLData_Unique_Product_Summary.LaneNumber
	AND SCLData_Unique_Sale_Summary.InvoiceNumber = SCLData_Unique_Product_Summary.InvoiceNumber
	AND SCLData_Unique_Sale_Summary.TransactionNumber = SCLData_Unique_Product_Summary.TransactionNumber
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-16 : 15:08:29
Looks like you need to add it before the second FROM too. A column is not available in the derived tables unless you put it in the SELECT. In the first derived table, you are using another derived table. You've got it in the SELECT of the second derived table but not in the outer one. So in the most outer portion, it is invalid.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-05-16 : 15:25:50
Do you mean like this


SELECT 	TOP 2 SCLData_Unique_Sale_Summary.*,
	SCLData_Unique_Product_Summary.ProductBarcode,
	SCLData_Unique_Product_Summary.ProductDescription,
	SCLData_Unique_Product_Summary.ActivePrice,
	SCLData_Unique_Product_Summary.TotalProductQuantity,
	SCLData_Unique_Product_Summary.TotalProductSalePrice,
	SCLData_Unique_Product_Summary.DepartmentDescription,
	SCLData_Unique_Product_Summary.SubDeptDescription,
	SCLData_Unique_Product_Summary.RoundedTax,
	CASE DATEPART( dw, AccountingDate )
		WHEN '1' THEN 'SUNDAY'
		WHEN '2' THEN 'MONDAY'
		WHEN '3' THEN 'TUESDAY'
		WHEN '4' THEN 'WEDNESDAY'
		WHEN '5' THEN 'THURSDAY'
		WHEN '6' THEN 'FRIDAY'
		WHEN '7' THEN 'SATURDAY'
	END AS AccountDateWeekDay,
	CASE DATEPART( m, AccountingDate )
		WHEN '1' THEN 'JAN'
		WHEN '2' THEN 'FEB'
		WHEN '3' THEN 'MAR'
		WHEN '4' THEN 'APR'
		WHEN '5' THEN 'MAY'
		WHEN '6' THEN 'JUN'
		WHEN '7' THEN 'JUL'
		WHEN '8' THEN 'AUG'
		WHEN '9' THEN 'SEP'
		WHEN '10' THEN 'OCT'
		WHEN '11' THEN 'NOV'
		WHEN '12' THEN 'DEC'
	END AS AccountDateMonth,
	CASE SCLData_Unique_Sale_Summary.StoreId
		WHEN '105' THEN '3001'
		WHEN '101' THEN '3024'
		WHEN '112' THEN '3025'
		WHEN '103' THEN '3003'
		WHEN '102' THEN '3002'
	END AS SubsidiaryId
FROM	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 
			ProductBarcode,
			MAX( ProductDescription ) ProductDescription,
			MAX( ActivePrice ) ActivePrice,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalProductQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalProductSalePrice,
			MAX( DepartmentDescription ) DepartmentDescription,
			MAX( SubDeptDescription ) SubDeptDescription
			,RoundedTax
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, RoundedTax, TransactionNumber, ProductBarcode
	) AS SCLData_Unique_Product_Summary,
	(
		SELECT
			StoreId, 
			LaneNumber, 
			InvoiceNumber, 
			TransactionNumber, 	
			RoundedTax,
			MAX( MagnaNumberShort ) MagnaNumberShort,
			MAX( TransactionType ) TransactionType,
			MIN( AccountingDate ) AccountingDate,
			MIN( TransactionStartTime ) TransactionStartTime,
			MAX( TransactionEndTime ) TransactionEndTime,
			CEILING( SUM( CONVERT( DECIMAL(10,2), Quantity ) ) ) TotalSaleQuantity,
			SUM( CONVERT( DECIMAL(10,2), DollarAmount ) ) TotalSalePrice
		FROM (
			SELECT DISTINCT
			F1068 AS TransactionType,
			F254 AS AccountingDate,
			F253 AS SystemDate,
			F1056 AS StoreId,
			F1057 AS LaneNumber,
			F1035 AS TransactionStartTime,
			F1036 AS TransactionEndTime,
			F1032 AS TransactionNumber ,
			F1764 AS InvoiceNumber,
			F1185 AS OperatorNumber,
			F1126 AS OperatorCode,
			F1127 AS OperatorName,
			F1148 AS MagnaNumberShort,
			F01 AS ProductBarcode,
			F02 AS ProductDescription, 
			F04 AS SubDepartmentCode,
			F03 AS DepartmentCode,
			F97 AS TaxableFlag,
			F113 AS ActivePriceDescription,
			F1101 AS ItemLineNumber,
			F1007 AS ActivePrice,
			F1006 AS ActiveQuantity,
			F65 AS DollarAmount,
			F64 AS Quantity, 
			F1263 AS RoundedTax,
			F238 AS DepartmentDescription,
			F1022 AS SubDeptDescription,
			F9999 AS XmlFile
			FROM SCLData_tab
		) AS SCLData_Unique_WorkTable
		WHERE ISNUMERIC( DollarAmount ) = 1
		AND ISNUMERIC( Quantity ) = 1
		AND StoreId BETWEEN '101' AND '112'
		GROUP BY StoreId, LaneNumber, InvoiceNumber, RoundedTax,TransactionNumber
	) AS SCLData_Unique_Sale_Summary
	WHERE SCLData_Unique_Sale_Summary.StoreId = SCLData_Unique_Product_Summary.StoreId
	AND SCLData_Unique_Sale_Summary.LaneNumber = SCLData_Unique_Product_Summary.LaneNumber
	AND SCLData_Unique_Sale_Summary.InvoiceNumber = SCLData_Unique_Product_Summary.InvoiceNumber
	AND SCLData_Unique_Sale_Summary.TransactionNumber = SCLData_Unique_Product_Summary.TransactionNumber
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-16 : 15:46:14
It's hard to say. Does it error still? Do you get the required data back? I can only help you get rid of the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-05-16 : 15:49:30
Yea it is running but I haven't gotten back any results as yet. I will tell you want it returns when it completes. Thanks for your help.
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