SQL and Crystal Script examples

SQL and Crystal Script examples.

SQL
Simple Update (One table, One Database).
Update from one table to a table same database.
Create Excel compatible .CSV files from a command line using ISQLW.

Crystal
To format a phone number.

SQL:

Simple Update (One table, one Database).

Example:

UPDATE Vendor
SET CountryCode = 'US'
WHERE CountryCode = 'USA'

UPDATE (table name)
SET (column name to update) = '(What you want in the column)'
WHERE (column name to look at for the where) = '(condition for the where)'

Update from one table to another. Same Database.

Example:

UPDATE P
SET P.VendorName = V.VendorName
FROM POHeader AS P, Vendor AS V
WHERE P.VendorCode = V.VendorCode

UPDATE (P is the letter assigned to the table to be updated)
SET (letter. Destination column name) = (letter. Source column name)
FROM (Destination table) AS P, (Source table) AS V
WHERE (letter. Column name to compare) = (letter. Column name to compare)

Create Excel compatible .CSV files from a command line using ISQLW.

The script. Save as a text .sql file:

declare @header varchar (256)
declare @heade2 Varchar (256)
declare @comma Varchar (1)
declare @note Varchar (75)
select @header = ('Name,Customer,Job #,Type,Status,Date,Received,Complete,Received,Complete,Received,Complete,Planed,Adjusted')
select @heade2 = (' , , , , ,Entered,Engineering,Engineering,Drafting,Drafting,Manufacturing,Manufacturing,Ship Date,Ship Date')
select @comma = (',')
select @note = ('Note! Dates showing "Jan 1 1900" indicate blank fields.')
Print @header
Print @heade2

SELECT LEFT (S.Name, 27),
@comma,
LEFT (C.CustomerName, 30),
@comma,
LEFT (O.JobNumber, 10),
@comma,
LEFT (O.ProjectTypeCode, 10),
@comma,
LEFT (O.JobStatusCode, 10),
@comma,
LEFT (O.DateEntered, 11),
@comma,
LEFT (E.ActualReceivedDate, 11),
@comma,
LEFT (E.ActualCompleteDate, 11),
@comma,
LEFT (D.ActualReceivedDate, 11),
@comma,
LEFT (D.ActualCompleteDate, 11),
@comma,
LEFT (M.ActualReceivedDate, 11),
@comma,
LEFT (M.ActualCompleteDate, 11),
@comma,
LEFT (O.ShipDate, 11),
@comma,
LEFT (O.ShipDateAdjusted, 11)

FROM OrderEntry AS O, Customer AS C, SalesRep AS S, Engineering AS E, Drafting AS D, Manufacturing AS M

WHERE (O.ProjectTypeCode = 'UtilityPol' or O.ProjectTypeCode = 'UtilityTow')
AND O.CustomerCode = C.CustomerCode
AND (O.JobStatusCode <> 'Shipped' and O.JobStatusCode <> 'Cancelled')
AND O.SalesRepCode2 = S.SalesRepCode
AND E.Jobnumber = O.Jobnumber
AND D.Jobnumber = O.Jobnumber
AND M.Jobnumber = O.Jobnumber
AND S.Name = 'Bobby Cox'

Order by C.CustomerName, O.JobNumber

PRINT @note

The Command Line:

C:\MSSQL7\Binn\isqlw.exe -h -S MyServer -d MyDataBase -U MyID -P MyPassword -i c:\mssql7\binn\MyInputScript.sql -o c:\MyOutputFile.CSV

Example of the output file:

BobbyCox.csv

Crystal:

To format a phone number.

Original number 8175551212. Formatted (817) 555-1212

If Trim({ado.Phone}) = "" Then
    ""
Else
    Left(Trim({ado.Phone}), 5) + " " +
    Mid(Trim({ado.Phone}), 6, 4) + "-" +
    Right(Trim({ado.Phone}), 4)

Site Navigation Bar