SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Substring / Distinct?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Crima
Starting Member

USA
17 Posts

Posted - 11/14/2012 :  23:48:31  Show Profile  Reply with Quote
Hi I am trying to figure out this problem and it has me stumped. That last part
"Where the first letter of the vendor name is N, O or P" Has me confused.
I thought of using SELECT DISTINCT or using a Substring, can I get some insight?

Problem:
Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns
three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT
statement that returns all of the columns in the view, sorted by VendorName, where the
first letter of the vendor name is N, O, or P.

My Code:

CREATE VIEW InvoiceBasic
--RETURN VendorName, InvoiceNumber, InvoiceTotal
SELECT VendorName, InvoiceNumber, InvoiceTotal
WHERE SUBSTRING(N, O, P)
ORDER BY VendorName


A witty student

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  03:33:45  Show Profile  Reply with Quote
Look up the syntax of substring (http://msdn.microsoft.com/en-us/library/ms187748.aspx). The Start and the length parameters of the function are in this case both 1. Then look up IN (http://msdn.microsoft.com/en-us/library/ms177682.aspx). Here's a hint for the final syntax: The substring of vendorname is in you collection of letters and each letter should be surrounded by single quotes.

- Lumbago
My blog-> http://thefirstsql.com

Edited by - Lumbago on 11/15/2012 03:34:27
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  05:23:17  Show Profile  Reply with Quote
you could just use

WHERE VendorName LIKE '[NOP]%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000