Redian新闻
>
Codes Re: 求教: Join 两个query (data)
avatar
Codes Re: 求教: Join 两个query (data)# Database - 数据库
w*i
1
OK, the following are the source codes. Actually I think they make the
problems more complicated. Thanks a million for your help!
Subquery 1 (works right) -- name of "hr_contact_add"
function: produce the address list of the people
SELECT [dbo_HRPEOPLE11].[PERSON_ID], [dbo_HRPEOPLE11].[NAME_LAST],
[dbo_HRPEOPLE11].[NAME_FIRST], [dbo_HRPEOPLE11].[NAME_MIDDLE],
[dbo_HRPEOPLE11].[NAME_PREFERRED], [dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
[dbo_HRPEOPLE_ADDRESSES11].[ADR_STREET1],
[dbo_HRPEOPLE_ADDR
avatar
n*f
2
Which Field is you just added?
Are you sure that[hr_contact_add].[PERSON_ID]
and [hr_contact_phone].[PERSON_ID] are both UNIQUE ?

[dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
[dbo_HRPEOPLE_ADDRESSES11].[ADR_STATE],
((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

【在 w******i 的大作中提到】
: OK, the following are the source codes. Actually I think they make the
: problems more complicated. Thanks a million for your help!
: Subquery 1 (works right) -- name of "hr_contact_add"
: function: produce the address list of the people
: SELECT [dbo_HRPEOPLE11].[PERSON_ID], [dbo_HRPEOPLE11].[NAME_LAST],
: [dbo_HRPEOPLE11].[NAME_FIRST], [dbo_HRPEOPLE11].[NAME_MIDDLE],
: [dbo_HRPEOPLE11].[NAME_PREFERRED], [dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
: [dbo_HRPEOPLE_ADDRESSES11].[ADR_STREET1],
: [dbo_HRPEOPLE_ADDR

avatar
aw
3
Or ([dbo_HRPEOPLE11].[PRIMARY_PHONE_TYPE]=Null)
这是你TYPE的还是COPY来的,至少=NULL不对。

[dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
[dbo_HRPEOPLE_ADDRESSES11].[ADR_STATE],
((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

【在 w******i 的大作中提到】
: OK, the following are the source codes. Actually I think they make the
: problems more complicated. Thanks a million for your help!
: Subquery 1 (works right) -- name of "hr_contact_add"
: function: produce the address list of the people
: SELECT [dbo_HRPEOPLE11].[PERSON_ID], [dbo_HRPEOPLE11].[NAME_LAST],
: [dbo_HRPEOPLE11].[NAME_FIRST], [dbo_HRPEOPLE11].[NAME_MIDDLE],
: [dbo_HRPEOPLE11].[NAME_PREFERRED], [dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
: [dbo_HRPEOPLE_ADDRESSES11].[ADR_STREET1],
: [dbo_HRPEOPLE_ADDR

avatar
w*i
4
I add "NAME_PREFERRED" in "hr_contact_add" table and the error message shows
up. I don't know whether it is exactly the problem. But yesterday this query
(Query 3) didn't give me any problem and I even used its results for another
two higher-level queries.
Yup, they are both unique since they are primary keys in their individual
tables.
Correct me if I were wrong here. thanks a lot!

((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T
([dbo_HRPEOPLE_PHONES11].[PHONE_TY

【在 n****f 的大作中提到】
: Which Field is you just added?
: Are you sure that[hr_contact_add].[PERSON_ID]
: and [hr_contact_phone].[PERSON_ID] are both UNIQUE ?
:
: [dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
: [dbo_HRPEOPLE_ADDRESSES11].[ADR_STATE],
: ((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

avatar
w*i
5
COPY, what is wrong? "=Null" and "IS NULL" both work, I think...but
i didn't check the reference and wrote it days ago. it didn't give me the
wrong results, so... I will check it.
thanks!

((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T
([dbo_HRPEOPLE_PHONES11].[PHONE_TYPE]=[dbo_HRPEOPLE11].[PRIMARY_PHONE_TYPE])

【在 aw 的大作中提到】
: Or ([dbo_HRPEOPLE11].[PRIMARY_PHONE_TYPE]=Null)
: 这是你TYPE的还是COPY来的,至少=NULL不对。
:
: [dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE],
: [dbo_HRPEOPLE_ADDRESSES11].[ADR_STATE],
: ((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

avatar
aw
6
i don't know access, but = null is wrong in sql server.
another thing you can try is to copy the first table and remove the new
column, run the same query and see if it can work fine as before.

((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

【在 w******i 的大作中提到】
: COPY, what is wrong? "=Null" and "IS NULL" both work, I think...but
: i didn't check the reference and wrote it days ago. it didn't give me the
: wrong results, so... I will check it.
: thanks!
:
: ((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T
: ([dbo_HRPEOPLE_PHONES11].[PHONE_TYPE]=[dbo_HRPEOPLE11].[PRIMARY_PHONE_TYPE])

avatar
w*i
7
i tried it and it could NOT work fine as b4...
the invalid operation msg shows as
"Invalid operation. (Error 3219)
Possible causes:
1. You tried to write to a read-only property. See the Help topic for the
property to determine whether it is read/write.
2. You tried to use a method or property on a type of Recordset object that
the method or property does not apply to. See the Recordset object summary
topic to determine which methods and properties apply to a given type of
Recordset object.
3. Y

【在 aw 的大作中提到】
: i don't know access, but = null is wrong in sql server.
: another thing you can try is to copy the first table and remove the new
: column, run the same query and see if it can work fine as before.
:
: ((([dbo_HRPEOPLE_ADDRESSES11].[ADDRESS_TYPE])=[dbo_HRPEOPLE11].[PRIMARY_MAIL_T

avatar
n*f
8
无依无系,
您如何发现这些错误信息的?
从SQL SERVER 还是您的 APPLICATION?
好象您是在运行某个应用程序是有错误.

that
the
the

【在 w******i 的大作中提到】
: i tried it and it could NOT work fine as b4...
: the invalid operation msg shows as
: "Invalid operation. (Error 3219)
: Possible causes:
: 1. You tried to write to a read-only property. See the Help topic for the
: property to determine whether it is read/write.
: 2. You tried to use a method or property on a type of Recordset object that
: the method or property does not apply to. See the Recordset object summary
: topic to determine which methods and properties apply to a given type of
: Recordset object.

avatar
w*i
9
I run it from Access2000 (Query) SQL View. Should it be the application?
thanks!

that

【在 n****f 的大作中提到】
: 无依无系,
: 您如何发现这些错误信息的?
: 从SQL SERVER 还是您的 APPLICATION?
: 好象您是在运行某个应用程序是有错误.
:
: that
: the
: the

avatar
n*f
10

NO. 有没有可能发这个ACCESS文件给我?

【在 w******i 的大作中提到】
: I run it from Access2000 (Query) SQL View. Should it be the application?
: thanks!
:
: that

avatar
aw
11
try to rebuild the queries.

the
summary
object
new
me

【在 w******i 的大作中提到】
: I run it from Access2000 (Query) SQL View. Should it be the application?
: thanks!
:
: that

avatar
w*i
12
I am afraid I cannot do that. It is a protected file and it is beyond 20M. :(

【在 n****f 的大作中提到】
:
: NO. 有没有可能发这个ACCESS文件给我?

avatar
w*i
13
i will do them tomorrow morning. thanks for your help.

of
"
think...but

【在 aw 的大作中提到】
: try to rebuild the queries.
:
: the
: summary
: object
: new
: me

avatar
n*f
14
We don't need the data.
You can send us the table structure with some fake rows.
So we can help you out.

【在 w******i 的大作中提到】
: I am afraid I cannot do that. It is a protected file and it is beyond 20M. :(
avatar
w*i
15
Thanks! I tried another way to do the nested queries and the problem got
solved.

application?

【在 n****f 的大作中提到】
: We don't need the data.
: You can send us the table structure with some fake rows.
: So we can help you out.

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。