Codes Re: 求教: Join 两个query (data) - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Codes Re: 求教: Join 两个query (data)

Codes Re: 求教: Join 两个query (data)# Database - 数据库

w*i2004-06-09 07:06

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

n*f2004-06-09 07:06

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

aw2004-06-09 07:06

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 的大作中提到】

w*i2004-06-09 07:06

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

w*i2004-06-09 07:06

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

aw2004-06-09 07:06

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])

w*i2004-06-09 07:06

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

n*f2004-06-09 07:06

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.

w*i2004-06-09 07:06

9 楼

I run it from Access2000 (Query) SQL View. Should it be the application?
thanks!

that

【在 n****f 的大作中提到】

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

n*f2004-06-09 07:06

10 楼

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

【在 w******i 的大作中提到】

: I run it from Access2000 (Query) SQL View. Should it be the application?
: thanks!
:
: that

aw2004-06-09 07:06

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

w*i2004-06-09 07:06

12 楼

I am afraid I cannot do that. It is a protected file and it is beyond 20M. :(

【在 n****f 的大作中提到】

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

w*i2004-06-09 07:06

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

n*f2004-06-09 07:06

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. :(

w*i2004-06-09 07:06

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.

2024-01-28 18:01

2024-01-09 19:01

2024-01-07 18:01

2024-01-06 18:01

2024-01-06 18:01

2023-12-13 17:12

2023-12-02 07:12

2023-11-26 18:11

中美航班重磅消息！每周85班，直飞更方便！

微软、亚马逊、谷歌大裁员！哪些科技和零售公司有缩减规模计划？

中国外长王毅：中美关系已经止跌回稳

脱轨事故造成20多人受伤联邦调查纽约市全部地铁系统

更多 2024-05-28 的新闻