Thomas Stockbruegger
asked on
Need some help with CREATE A VIEW
Hello,
I need some help with CREATE a VIEW.
sql.Format("CREATE VIEW ViewLieferscheine2 AS SELECT\
A.LieferscheinNr, A.VersandName1, A.VersandName2,\
A.VersandStrassePostfach,C AST(A.Vers andPostlei tzahl AS VARCHAR(10))AS VersandPostleitzahl,\
A.VersandOrt,A.VersandLand ,A.Liefers cheindatum ,\
A.DebitorenNr, A.Lieferungsbedingungenkur zbezeichnu ng AS Lieferbedingung,\
A.FakturiertAktiv AS Fakturiert,SUM(B.Einheitbe z13wert) AS Gewicht\
FROM Lieferscheine AS A inner join Lieferscheinpositionen AS B\
ON A.LieferscheinNr=B.Liefers cheinNr WHERE A.LieferscheinNr<2000000\
GROUP BY A.LieferscheinNr,A.Versand Name1,A.Ve rsandName2 ,\
A.VersandStrassePostfach, A.VersandPostleitzahl,\
A.VersandOrt,A.VersandLand , A.Lieferscheindatum, A.DebitorenNr,\
A.Lieferungsbedingungenkur zbezeichnu ng, A.FakturiertAktiv");
This view works just fine, but I would like to add data from a other table.
A.Lieferungsbedingungenkur zbezeichnu ng AS Lieferbedingung = is a number from 0 to 10 (type nvachar)
In a thrid table Lieferterms are the explication to the numbers (nvachar)
Table Lieferterms
Columns
-------------------------- ------
Number Terms
(nvachar) (nvachar)
-------------------------- ---------
0 pick-up service by car
1 pick-up service by train
2 and so on……..
I would like to add these explications to the ViewLieferscheine2 in Lieferbedingung
How can I do this?
So f.e. in Lieferbedingung of my view above should the explication like pick-up serivce by car and not the number 0
500 points with a solution.
Thank you.
Best regards,
Thomas
I need some help with CREATE a VIEW.
sql.Format("CREATE VIEW ViewLieferscheine2 AS SELECT\
A.LieferscheinNr, A.VersandName1, A.VersandName2,\
A.VersandStrassePostfach,C
A.VersandOrt,A.VersandLand
A.DebitorenNr, A.Lieferungsbedingungenkur
A.FakturiertAktiv AS Fakturiert,SUM(B.Einheitbe
FROM Lieferscheine AS A inner join Lieferscheinpositionen AS B\
ON A.LieferscheinNr=B.Liefers
GROUP BY A.LieferscheinNr,A.Versand
A.VersandStrassePostfach, A.VersandPostleitzahl,\
A.VersandOrt,A.VersandLand
A.Lieferungsbedingungenkur
This view works just fine, but I would like to add data from a other table.
A.Lieferungsbedingungenkur
In a thrid table Lieferterms are the explication to the numbers (nvachar)
Table Lieferterms
Columns
--------------------------
Number Terms
(nvachar) (nvachar)
--------------------------
0 pick-up service by car
1 pick-up service by train
2 and so on……..
I would like to add these explications to the ViewLieferscheine2 in Lieferbedingung
How can I do this?
So f.e. in Lieferbedingung of my view above should the explication like pick-up serivce by car and not the number 0
500 points with a solution.
Thank you.
Best regards,
Thomas
sql.Format("CREATE VIEW ViewLieferscheine2 AS SELECT\
A.LieferscheinNr, A.VersandName1, A.VersandName2,\
A.VersandStrassePostfach,CAST(A.VersandPostleitzahl AS VARCHAR(10))AS VersandPostleitzahl,\
A.VersandOrt,A.VersandLand,A.Lieferscheindatum,\
A.DebitorenNr, A.Lieferungsbedingungenkurzbezeichnung AS Lieferbedingung,\
A.FakturiertAktiv AS Fakturiert,SUM(B.Einheitbez13wert) AS Gewicht\
FROM Lieferscheine AS A inner join Lieferscheinpositionen AS B\
ON A.LieferscheinNr=B.LieferscheinNr WHERE A.LieferscheinNr<2000000\
GROUP BY A.LieferscheinNr,A.VersandName1,A.VersandName2,\
A.VersandStrassePostfach, A.VersandPostleitzahl,\
A.VersandOrt,A.VersandLand, A.Lieferscheindatum, A.DebitorenNr,\
A.Lieferungsbedingungenkurzbezeichnung, A.FakturiertAktiv");
ASKER
I got this error.
I did a mistake.
The Terms in Table Lieferterms are not nvarchar the type is ntext.
Does the error comes from that?
Please help, thanks
1.BMP
I did a mistake.
The Terms in Table Lieferterms are not nvarchar the type is ntext.
Does the error comes from that?
Please help, thanks
1.BMP
ASKER
in english: you only can compare or sort the typs text,ntext or image with IS NULL or LIKE
Can you help me?
Can you help me?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you, that works.
Have a great weekend.
Best regards,
Thomas
Have a great weekend.
Best regards,
Thomas
Glad I could help you :)
Open in new window