August 11, 2008 at 7:58 am
Hello,
I try to create the following view:
CREATE VIEW TOPDESK AS
SELECT
cast(wnr.WNS_ID as varchar) PERSONEELSNUMMER,
wnr.ACHTERNAAM,
wnr.ROEPNAAM VOORNAAM,
wnr.VOORL VOORLETTERS,
wnr.VOORV TUSSENVOEGSELS,
wnr.GESLACHT,
cast(csa.AFD_ID as varchar) UNIT ,
orgUnitML.name AFDELING,
HRMFunctionML.name FUNCTIE,
con.TELEFOON,
con.TELEFOON FAX,
con.DAT_IND,
cast(con.WNS_ID as varchar),
con.DAT_UIT,
wnr.NAAM TMP1,
wnr.AANSPR_NAAM TMP2
FROM
csa,
con,
dvb,
wnr,
wg1 wg1_fnk,
HRMFunctionML,
orgUnitML
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( HRMFunctionML.companyID=wg1_fnk.bed_id_fnk )
AND ( csa.fnk_id=HRMFunctionML.functionID )
AND ( csa.afd_id=orgUnitML.orgUnitID )
AND ( HRMFunctionML.UILanguageID='NED' )
AND ( orgUnitML.UIlanguageID='NED' )
AND ( csa.bed_id=1 )
AND (
con.DAT_IND <= getdate()
AND (con.DAT_UIT >= getdate()
OR con.DAT_UIT IS NULL )
AND csa.DAT_ING = ANY (SELECT
max(csa.DAT_ING)
FROM
csa
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( csa.bed_id=1 )
AND (
csa.DAT_ING <= getdate()
AND csa.WNS_ID = wnr.WNS_ID
)
)
AND wnr.VOORV IS NULL
AND ascii(substring(wnr.ACHTERNAAM,1,1)) <= '90'
)
UNION
SELECT
cast(wnr.WNS_ID as varchar)PERSONEELSNUMMER,
wnr.ACHTERNAAM,
wnr.ROEPNAAM,
wnr.VOORL,
' ' as VOORV,
wnr.GESLACHT,
cast(csa.AFD_ID as varchar)UNIT,
orgUnitML.name AFDELING,
HRMFunctionML.name FUNCTIE,
con.TELEFOON,
con.TELEFOON FAX,
con.DAT_IND,
cast(con.WNS_ID as varchar),
con.DAT_UIT,
wnr.NAAM,
wnr.AANSPR_NAAM
FROM
csa,
con,
dvb,
wnr,
wg1 wg1_fnk,
HRMFunctionML,
orgUnitML
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( HRMFunctionML.companyID=wg1_fnk.bed_id_fnk )
AND ( csa.fnk_id=HRMFunctionML.functionID )
AND ( csa.afd_id=orgUnitML.orgUnitID )
AND ( HRMFunctionML.UILanguageID='NED' )
AND ( orgUnitML.UIlanguageID='NED' )
AND ( csa.bed_id=1 )
AND (
con.DAT_IND <= getdate()
AND (con.DAT_UIT >= getdate()
OR con.DAT_UIT IS NULL )
AND csa.DAT_ING = ANY (SELECT
max(csa.DAT_ING)
FROM
csa
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( csa.bed_id=1 )
AND (
csa.DAT_ING <= getdate()
AND csa.WNS_ID = wnr.WNS_ID
)
)
AND wnr.VOORV IS NOT NULL
AND ascii(substring(wnr.ACHTERNAAM,1,1)) <= '90'
)
UNION
SELECT
cast(wnr.WNS_ID as varchar) PERSONEELSNUMMER,
substring(wnr.ACHTERNAAM,datalength(wnr.VOORV)+2,50),
wnr.ROEPNAAM,
wnr.VOORL,
wnr.VOORV,
wnr.GESLACHT,
cast(csa.AFD_ID as varchar) UNIT,
orgUnitML.name AFDELING,
HRMFunctionML.name FUNCTIE,
con.TELEFOON,
con.TELEFOON FAX,
con.DAT_IND,
cast(con.WNS_ID as varchar),
con.DAT_UIT,
wnr.NAAM,
wnr.AANSPR_NAAM
FROM
csa,
con,
dvb,
wnr,
wg1 wg1_fnk,
HRMFunctionML,
orgUnitML
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( HRMFunctionML.companyID=wg1_fnk.bed_id_fnk )
AND ( csa.fnk_id=HRMFunctionML.functionID )
AND ( csa.afd_id=orgUnitML.orgUnitID )
AND ( HRMFunctionML.UILanguageID='NED' )
AND ( orgUnitML.UIlanguageID='NED' )
AND ( csa.bed_id=1 )
AND (
con.DAT_IND <= getdate()
AND (con.DAT_UIT >= getdate()
OR con.DAT_UIT IS NULL )
AND csa.DAT_ING = ANY (SELECT
max(csa.DAT_ING)
FROM
csa
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( csa.bed_id=1 )
AND (
csa.DAT_ING <= getdate()
AND csa.WNS_ID = wnr.WNS_ID
)
)
AND wnr.VOORV IS NOT NULL
AND ascii(substring(wnr.ACHTERNAAM,1,1)) > '90'
)
UNION
SELECT
cast(wnr.WNS_ID as varchar)PERSONEELSNUMMER,
substring(wnr.ACHTERNAAM,(datalength(wnr.ACHTERNAAM)-datalength(wnr.NAAM)-datalength(wnr.AANSPR_NAAM)-2),50),
wnr.ROEPNAAM,
wnr.VOORL,
wnr.VOORV,
wnr.GESLACHT,
cast(csa.AFD_ID as varchar)UNIT,
orgUnitML.name AFDELING,
HRMFunctionML.name FUNCTIE,
con.TELEFOON,
con.TELEFOON FAX,
con.DAT_IND,
cast(con.WNS_ID as varchar),
con.DAT_UIT,
wnr.NAAM,
wnr.AANSPR_NAAM
FROM
csa,
con,
dvb,
wnr,
wg1 wg1_fnk,
HRMFunctionML,
orgUnitML
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( HRMFunctionML.companyID=wg1_fnk.bed_id_fnk )
AND ( csa.fnk_id=HRMFunctionML.functionID )
AND ( csa.afd_id=orgUnitML.orgUnitID )
AND ( HRMFunctionML.UILanguageID='NED' )
AND ( orgUnitML.UIlanguageID='NED' )
AND ( csa.bed_id=1 )
AND (
con.DAT_IND <= getdate()
AND (con.DAT_UIT >= getdate()
OR con.DAT_UIT IS NULL )
AND csa.DAT_ING = ANY (SELECT
max(csa.DAT_ING)
FROM
csa
WHERE
( wnr.bed_id=dvb.bed_id and wnr.wns_id=dvb.wns_id )
AND ( dvb.bed_id=con.BED_ID and dvb.wns_id=con.WNS_ID and dvb.dvb_id=con.DVB_ID )
AND ( con.BED_ID=csa.bed_id and con.WNS_ID=csa.wns_id and con.DVB_ID=csa.dvb_id and con.CON_ID=csa.con_id )
AND ( csa.bed_id=1 )
AND (
csa.DAT_ING <= getdate()
AND csa.WNS_ID = wnr.WNS_ID
)
)
AND wnr.VOORV IS NULL
AND ascii(substring(wnr.ACHTERNAAM,1,1)) > '90'
)
When I execute this command I got the following error:
Msg 4511, Level 16, State 1, Procedure TOPDESK, Line 2
Create View or Function failed because no column name was specified for column 13.
if I only parse the statement it gives:
Command(s) completed successfully.
When I run the statement without "Create view topdesk", I get a resultset.
The above is done in SQL server Management Studio.
August 11, 2008 at 8:05 am
This is column 13 in the first select of the union:
cast(con.WNS_ID as varchar),
And notice that you do not provide an alias. If you provide an alias the create view should work fine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2008 at 10:56 am
Thx Jack for your reply.
After creating the alias everything works fine.
John
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply