[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MSSQL 2014 Query Synthax

Posted on 2016-11-04
8
Medium Priority
?
87 Views
Last Modified: 2016-11-11
Hello Experts-

I am reaching out because I am a bit stumped with a query.

QUERY w/ Issue:

SELECT 
			arDate,
			referenceNo,
			custName
			
        FROM (SELECT
                        a.ar_date as [arDate],
						   a.arroNO as [referenceNo],
						   b.bname	as [custName]
							

                FROM AR AS a
                INNER JOIN customer AS b ON a.store_UIN = b.store_UIN 

				 WHERE a.store_UIN = 'FL006' and b.store_UIN = 'FL006'      
				
            ) AS Gdata
   

Open in new window


CUSTOMER TABLE SCHEMA:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_PRECISION_RADIX	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME
ROWStage	dbo	Customer	BOXFER	1	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FNAME	2	NULL	YES	nvarchar	40	80	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	LNAME	3	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	BNAME	4	NULL	YES	nvarchar	61	122	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ADDRESS	5	NULL	YES	nvarchar	29	58	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CITY	6	NULL	YES	nvarchar	24	48	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE1	7	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CUST_NO	8	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE2	9	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE3	10	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE4	11	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE5	12	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE6	13	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	STATE	14	NULL	YES	nvarchar	2	4	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ZIP	15	NULL	YES	nvarchar	10	20	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CNOTES	16	NULL	YES	nvarchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT1	17	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT2	18	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT3	19	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT4	20	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT5	21	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT6	22	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC1	23	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC2	24	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC3	25	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC4	26	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC5	27	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC6	28	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	SPENT	29	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NEW_LETTER	30	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	REPEAT_LTR	31	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	VISITS	32	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	OPEN_ACCT	33	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	BILLING	34	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	LABOR	35	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PARTS	36	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SUBLET	37	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NONTAXABLE	38	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	LAST_VISIT	39	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	LEVEL	40	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PO_REQ	41	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FIN_CRG	42	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL	43	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FLEET	44	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CREDITLIMIT	45	((0))	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FLEET_NO	46	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TAXID	47	('')	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	GSTNONTAXABLE	48	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	GSTTAXID	49	('')	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TSPENT	50	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	TVISITS	51	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NO_FEE	52	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PhDisplay	53	NULL	YES	nvarchar	6	12	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TAXESID	54	NULL	YES	nvarchar	4	8	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PromptTaxID	55	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FTFleetID	56	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	row_GUID	57	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FTNationalAccountID	58	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FleetID	59	NULL	YES	nvarchar	15	30	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FTNationalAccountNumber	60	NULL	YES	nvarchar	25	50	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL2	61	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL3	62	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail1	63	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail2	64	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail3	65	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FIRST_VISIT	66	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NationalAccount	67	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NationalAccountID	68	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	MemberID	69	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	MemberIDSuffix	70	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ClubCode	71	NULL	YES	nvarchar	5	10	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	Birthday	72	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	store_UIN	73	NULL	NO	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL

Open in new window


AR TABLE SCHEMA:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_PRECISION_RADIX	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME
ROWStage	dbo	AR	BOXFER	1	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	ARCUSTNO	2	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	ARRONO	3	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_DATE	4	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_AMOUNT	5	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_DESC	6	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_BILLING	7	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAY_DAY	8	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAY_REF	9	NULL	YES	nvarchar	16	32	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_INV_DTE	10	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_SENT	11	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	LST_INV_DT	12	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAID	13	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PO_NUM	14	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	TYPE	15	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	TroutID	16	NULL	YES	nvarchar	30	60	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	ARLINK	17	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	row_GUID	18	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	ARPAYMENTLINK	19	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PAYTYPE_GUID	20	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PAYTYPERELATION_GUID	21	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	CREATED	22	(getdate())	NO	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	store_UIN	23	NULL	NO	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL

Open in new window



PROBLEM:


When I run the query above I get a result set of 53,712 records. This is incorrect. What I notice is that in the output for referenceNo every record has that first record duplicate, although, the customer name changes.

When I run query
select * from AR

Open in new window

I get 16 records which matches a report I am trying to duplicate.

What I want to do is query the data in the AR table and also query the data in the Customer table do only grab the data from the AR and grab only the name that matches each of those 16 records.

Can you help me figure out where I am incorrect?
0
Comment
Question by:sj77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41874034
are these 2 tables only linked by field: store_UIN in each table or it need to be linked with more than one field as mentioned?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41874041
and is it possible to have same store_UIN in table: AR?

if yes, then you probably need to use a Group By clause in your Select SQL statement.
0
 

Author Comment

by:sj77
ID: 41874268
Store_UIN was added in. It basically is the unique ID for each record to its pertinent store. row_GUID was the original unique ID connecting the two tables.

So you say use group by?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41874280
can you provide some sample data and the current and expected output?
0
 

Author Comment

by:sj77
ID: 41874340
Ryan of which, the erroneous data or the way its supposed to come out? Cause if I query select * from AR I get the right amount of records.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41874741
Customer should almost certainly be joined on customer number, probably with store uin as well:

SELECT
                  arDate,
                  referenceNo,
                  custName
                  
        FROM (SELECT
                        a.ar_date as [arDate],
                                       a.arroNO as [referenceNo],
                                       b.bname      as [custName]
                                          

                FROM AR AS a
                INNER JOIN customer AS b ON a.store_UIN = b.store_UIN and a.ARCUSTNO = b.CUST_NO

                         WHERE a.store_UIN = 'FL006' and b.store_UIN = 'FL006'      
                        
            ) AS Gdata
0
 

Author Comment

by:sj77
ID: 41880529
Sorry for the delayed response, I was out of the office. I will test these out today and let you all know. Thank you!
0
 

Author Closing Comment

by:sj77
ID: 41883756
This was the fix to my query, thank you, Scott!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question