Any Help? - SQL Server Forums

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

Transact-SQL (2000)

Any Help?

Author

Topic

friscapjtn
Starting Member

8 Posts

Posted - 2009-09-04 : 03:00:56

get the following message:

"Server: Msg 8163, Level 16, State 1, Line 3
The text, ntext, or image data type cannot be selected as DISTINCT."

And I'm running the following query:

create view v_vof_RProseskomplain1 as
select a.* , b.kategori_komplain ,b.accountno ,b.nama_komplainer, b.no_telepon, b.isi, datediff(d,a.tanggal, getdate()) as lamaproses,
'' as keterangan from v_vof_proseskomplain a, vof_inputkomplain b, v_vof_finishkomplain c
where a.no_komplain = b.no_komplain and a.no_komplain = c.no_komplain
union
select a.no_proses, a.no_komplain, a.BranchId, a.tanggal, a.PIC_yg_menangani, a.divisi_PIC, a.BranchPIC, a.alasan, a.deletests, a.CreatorId, a.Createdate, a.LastuserId, a.Lastupdate, a.kategori_id, b.kategori_komplain, b.accountno, b.nama_komplainer, b.no_telepon,
b.isi, datediff(d,a.tanggal, getdate()) as lamaproses, '' as keterangan, '' as branch_name
from vof_hist_proseskomplain a, vof_hist_inputkomplain b
where a.no_komplain = b.no_komplain

How do I solve this error?

Hmmm....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 03:41:44

If there is no DISTINCT needed you can use UNION ALL instead of UNION.

No, you're never too old to Yak'n'Roll if you're too young to die.

wawansur
Starting Member

44 Posts

Posted - 2009-09-04 : 03:56:34

i don't see distinct syntax in your query, could you give table and column name. May be you can mail me in wawansdi@gmail.com and use Bahasa Indonesia. My English is not enough for explaining.

I'm nothing just gonna being

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 04:03:31

Hi wawansur,
the DISTINCT is implicit because of using UNION and not UNION ALL.

No, you're never too old to Yak'n'Roll if you're too young to die.

friscapjtn
Starting Member

8 Posts

Posted - 2009-09-04 : 04:40:16

create view test as
select a.no_komplain, a.branch_name, a.BranchId, a.tanggal as tanggal_input, a.kategori_komplain, a.accountno,
a.nopolisi, a.norangka, a.nomesin,a.line_id, a.nama_komplainer, a.no_telepon, a.isi, a.status,
a.creatorid as user_input,
b.no_proses, b.tanggal as tanggal_proses,
b.pic_yg_menangani, b.divisi_pic,b.branchpic,b.alasan,b.creatorid as user_proses,
'' as no_finish, '' as tanggal_finish, '' as keterangan, '' as user_finish,
(case when a.status= 'DONE'then a.lastuserid else null end )as userdone ,
(case when a.status= 'DONE'then a.lastupdate else null end )as tanggal_done ,
datediff (d,a.tanggal, b.tanggal) as lamainput_proses ,
(case when a.status= 'DONE'then datediff(d,a.tanggal,a.lastupdate) else null end )as lamainput_done
from v_vof_Rinputkomplain a left outer join v_vof_RProseskomplain1 b on a.no_komplain = b.no_komplain
union
select a.no_komplain, '' as branch_name, a.BranchId, a.tanggal, a.kategori_komplain, a.accountno, a.nopolisi, a.norangka, a.nomesin,
a.line_id, a.Nama_komplainer, a.no_telepon, a.isi, a.status, a.CreatorId as user_input,
b.no_proses, b.tanggal as tanggal_proses, b.pic_yg_menangani, b.divisi_PIC, b.BranchPIC, b.alasan,
b.CreatorId as user_proses, '' as no_finish, '' as tanggal_finish, '' as keterangan, '' as user_finish,
(case when a.status= 'DONE'then a.lastuserid else null end )as userdone ,
(case when a.status= 'DONE'then a.lastupdate else null end )as tanggal_done ,
datediff (d,a.tanggal, b.tanggal) as lamainput_proses,
(case when a.status= 'DONE'then datediff(d,a.tanggal,a.lastupdate) else null end )as lamainput_done
from dbo.vof_hist_inputkomplain a left outer join dbo.vof_hist_proseskomplain b on a.no_komplain = b.no_komplain

Hmmm....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 04:51:01

Hmmm... What?

No, you're never too old to Yak'n'Roll if you're too young to die.

friscapjtn
Starting Member

8 Posts

Posted - 2009-09-04 : 04:56:23

@webfred
it's only my signature. If you want to know my question, you can look above.

Hmmm....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 04:57:20

quote:
Originally posted by webfred

If there is no DISTINCT needed you can use UNION ALL instead of UNION.

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

wawansur
Starting Member

44 Posts

Posted - 2009-09-04 : 05:27:56

quote:
Originally posted by webfred

Hi wawansur,
the DISTINCT is implicit because of using UNION and not UNION ALL.

No, you're never too old to Yak'n'Roll if you're too young to die.

thx

I'm nothing just gonna being

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources