Solved

Need some help with CREATE A  VIEW

Posted on 2011-03-10
6
351 Views
Last Modified: 2012-05-11
Hello,
I need some help with CREATE a VIEW.

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");

This view works just fine, but I would like to add data from a other table.

A.Lieferungsbedingungenkurzbezeichnung 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

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");

Open in new window

0
Comment
Question by:tsp2002
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35093217
try this
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,C.Terms\
       A.FakturiertAktiv AS Fakturiert,SUM(B.Einheitbez13wert) AS Gewicht\

       FROM Lieferscheine AS A inner join Lieferscheinpositionen AS B\
       ON A.LieferscheinNr=B.LieferscheinNr\
       inner join Lieferterms AS C on A.Lieferungsbedingungenkurzbezeichnung = C.Number\ 
       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, C.Terms,A.FakturiertAktiv");

Open in new window

0
 

Author Comment

by:tsp2002
ID: 35093607
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
0
 

Author Comment

by:tsp2002
ID: 35094226
in english: you only can compare or sort  the typs text,ntext or image with IS NULL or LIKE
Can you help me?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35103598
try this.
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,convert(nvarchar,C.Terms) Terms\
       A.FakturiertAktiv AS Fakturiert,SUM(B.Einheitbez13wert) AS Gewicht\

       FROM Lieferscheine AS A inner join Lieferscheinpositionen AS B\
       ON A.LieferscheinNr=B.LieferscheinNr\
       inner join Lieferterms AS C on A.Lieferungsbedingungenkurzbezeichnung = C.Number\ 
       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, convert(nvarchar,C.Terms),A.FakturiertAktiv");

Open in new window

0
 

Author Closing Comment

by:tsp2002
ID: 35106386
thank you, that works.
Have a great weekend.
Best regards,
Thomas
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35106394
Glad I could help you :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now