This SHAPE for the ACCESS statement used SQL SERVER when how to rewrite-CodePudding

This SHAPE for the ACCESS statement used SQL SERVER when how to rewrite the
SHAPE {the SELECT * from yhxx} AS Command1 COMPUTE Command1 AS Command2 BY 'user id', 'user name'

Thank you,

CodePudding user response:

Repeated questions

CodePudding user response:

reference 1st floor Tiger_Zhao response:

repeating questions

First thank TIGER_ZHAO, you answered my many questions, but don't tube repeat repeat no questions, I'm in a hurry with, I need the answer, at the moment,

CodePudding user response:

 SHAPE {the SELECT * from yhxx} AS Command1 
COMPUTE Command1 alias are needed - aggregation function, Command1 is the alias, and then take the alias probably wrong grammar 
BY [user id], [username] - column names to use square brackets, single quotation marks are string values

CodePudding user response:

The

reference 3 floor Tiger_Zhao response:

 SHAPE {the SELECT * from yhxx} AS Command1 
COMPUTE Command1 alias are needed - aggregation function, Command1 is the alias, and then take the alias probably wrong grammar 
BY [user id], [username] - column names to use square brackets, single quotation marks are string values

Thank you very much, so please help me to look at, the following is a complete statement:

SHAPE {SELECT c. user number, c. username, c. village, c. floor, c. unit number, c. room number, c. table number, c. phenotypic name, c. billing type, c. billing category, c. monovalent, c. purchase number, c. purchases, c. purchase amount, c. operation date, c. operator, c. operations,
C. total number of operations, c. operation type, c. paper number, c. effectiveness, d. last time balance, d. cash balances, paid-in amount FROM
(
(
SELECT b. user number, user name, b. b. village, building number, b. b. unit number, room number, b. b. table number, name of phenotype, b. b. billing type, b. billing category, unit price, b. b. buy number, purchases, b. b. purchase amount, operation date, b. b. operator,
B. b. operating frequency, total number of operation, operation type, b. b. paper number, validity, b. b. fixed number
The FROM
(SELECT * FROM yhxx WHERE user id=3) AS a
LEFT the JOIN
(SELECT * FROM SDJL WHERE paper number=1 AND effectiveness=type='buy' AND 'effective' ORDER BY serial number) AS b
User id=ON a. b. user id
) AS c
LEFT the JOIN (SELECT * FROM yueb WHERE paper number=1) AS d ON c. user id=d. user number AND c. notes number=d. note number
)
The order by c. user number, c. table number, c. operation date
AS} AS Command1 COMPUTE Command1 Command2 BY 'user id', 'user name', 'community', 'building date', 'unit number', 'room number', 'bill number', 'the last balance', 'cash balances',' paid-in amount ', 'operator' and 'operation type'

CodePudding user response:

The

reference 3 floor Tiger_Zhao response:

 SHAPE {the SELECT * from yhxx} AS Command1 
COMPUTE Command1 alias are needed - aggregation function, Command1 is the alias, and then take the alias probably wrong grammar 
BY [user id], [username] - column names to use square brackets, single quotation marks are string values

Please help to analyze, where there is wrong? Thank you very much!

CodePudding user response:

1) the subquery cannot have the ORDER BY
The outermost layer of the FROM (2)... ) lack of alias
3) don't need the as Command2
4) column names to use square brackets

CodePudding user response:

You put inside the {} a correction in the query editor,

CodePudding user response:

The

reference 7 floor Tiger_Zhao response:

you put inside the {} a correction in the query editor,

Ok, thank you very much, I try quickly,

CodePudding user response:

The

reference 7 floor Tiger_Zhao response:

you put inside the {} a correction in the query editor,

The end result is changed to:

SHAPE {SELECT c. user number, c. username, c. village, c. floor, c. unit number, c. room number, c. table number, c. phenotypic name, c. billing type, c. billing category, c. monovalent, c. purchase number, c. purchases, c. purchase amount, c. operation date, c. operator, c. operations,
C. total number of operations, c. operation type, c. paper number, c. effectiveness, d. last time balance, d. cash balances, paid-in amount FROM
(
SELECT b. user number, user name, b. b. village, building number, b. b. unit number, room number, b. b. table number, name of phenotype, b. b. billing type, b. billing category, unit price, b. b. buy number, purchases, b. b. purchase amount, operation date, b. b. operator,
B. b. operating frequency, total number of operation, operation type, b. b. paper number, validity, b. b. fixed number
The FROM
(SELECT * FROM yhxx WHERE user id=3) AS a
LEFT the JOIN
(SELECT * FROM SDJL WHERE paper number=1 AND effectiveness=type='buy' AND 'effective') AS b
User id=ON a. b. user id
) AS c
LEFT the JOIN (SELECT * FROM yueb WHERE paper number=1) AS d ON c. user id=d. user number AND c. notes number=d. note number

} AS Command1 COMPUTE Command1 BY [user id], [username], [village], [f], [unit], [number] room, the instrument [number] [the last balance], [cash balances], [paid-in amount], [operator], [type] operation

Verified within {} statement results returned to normal, but the statement execution when there is a error:
[Microsoft] [ODBC SQL Server Driver] syntax error or violation of access rules

Please help to analyze, thank you,

CodePudding user response:

The ConnectionString how to write?
Using SHAPE has a dedicated driver,

CodePudding user response:

I do not know why, see as a variable name in Chinese program is big!