• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

SQL script using a variable

Hi, I am trying to solve the following question : Write a script that counts the number of books that have been ordered by at least one customer from California.  Count each individual title only once, even if more than one CA customers has ordered it. The output should say "Number of titles that have been ordered from CA: 42", with the correct value in place of 42.
Table 1 = tblorders
Table 2 = tblcustomers

They have custid as a common table.  Any idea?  





DECLARE @B int;
 
SET @B = (SELECT COUNT(*) 
 
FROM tblbooks bo JOIN tblcustomers c ON bo.custid = c.custid
 
WHERE custstat = 'CA');
 
IF @B count > 0 
		Print 'Number of titles that have been ordered from CA: + CAST @B AS VARCAR(64)'
		Print
 
ELSE 
 
	 Print'There are not any books ordered'

Open in new window

0
iolike
Asked:
iolike
  • 6
  • 3
  • 3
  • +1
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
Try this it should do the trick.  You did not say what the column for Title was so change bo.Title to the right column.
DECLARE @B int;
 
SET @B = (SELECT COUNT(DISTINCT bo.Title) 
FROM tblbooks bo JOIN tblcustomers c ON bo.custid = c.custid
WHERE custstat = 'CA');
 
IF @B count > 0 
		Print 'Number of titles that have been ordered from CA: + CAST @B AS VARCAR(64)'
		Print
 
ELSE 
 
	 Print'There are not any books ordered'

Open in new window

0
 
iolikeAuthor Commented:
I tried this code

DECLARE @B int;
 
SET @B = (SELECT COUNT(DISTINCT bo.bkTitle)
FROM tblbooks bo , tblcustomers c, tblorders o, tblorderlines or
WHERE c.custstat = 'CA')
 
IF @B count > 0
            Print 'Number of titles that have been ordered from CA: + CAST @B AS VARCAR(64)'
            Print
 
ELSE
 
       Print 'There are not any books ordered'

I got the following errors

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'or'.
Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'count'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'ELSE'.
0
 
SharathData EngineerCommented:
Try this. I am curios about your tables in FROM clause. Why do you want tblorders o, tblorderlines in the FROM clause if you are interested in COUNT(DISTINCT bo.bkTitle). Also there is no JOIN condition. Do you want to CROSS JOIN those tables?
DECLARE @B int;
 
SET @B = (SELECT COUNT(DISTINCT bo.bkTitle) 
FROM tblbooks bo , tblcustomers c, tblorders o, tblorderlines 
WHERE c.custstat = 'CA')
 
IF @B > 0 
BEGIN
            Print 'Number of titles that have been ordered from CA: + CAST @B AS VARCAR(64)'
            Print 'something'
END 
ELSE 
 
       Print 'There are not any books ordered'

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you want to include the tables tblorders, tblcustomers then join the tables in the Select query using appropriate joins.

Slight Syntactical Mistake
DECLARE @B int;
SET @B = (SELECT COUNT(*) 
FROM tblbooks bo JOIN tblcustomers c ON bo.custid = c.custid
WHERE custstat = 'CA');
 
IF @B > 0 
    Print 'Number of titles that have been ordered from CA: ' + @B;
ELSE 
    Print 'There are not any books ordered';

Open in new window

0
 
iolikeAuthor Commented:
Guru,
Ther reason I have all the tables in the FROM clause is tblbooks and customers do not have a common column
The relevant columns in each table is / are

Tblbooks has bkid

tblcustomer, custid

tblorders ordernumber, custid

tblorderlines bkid, ordernumber

I got the following answer when I cross joined all the tables

Number of titles that have been ordered from CA: + CAST @B AS VARCAR(64)
something
 



0
 
SharathData EngineerCommented:
try this
DECLARE @B int;
 
SET @B = (SELECT COUNT(DISTINCT bo.bkTitle) 
FROM tblbooks bo , tblcustomers c, tblorders o, tblorderlines 
WHERE c.custstat = 'CA')
 
IF @B > 0 
BEGIN
            Print 'Number of titles that have been ordered from CA: ' + CAST (@B AS VARCAR(64))
            Print 'something'
END 
ELSE 
 
       Print 'There are not any books ordered'

Open in new window

0
 
SharathData EngineerCommented:
you can delete Print 'something' statement if you don't want that.
0
 
SharathData EngineerCommented:
typo in previous post.

DECLARE @B int;
 
SET @B = (SELECT COUNT(DISTINCT bo.bkTitle) 
FROM tblbooks bo , tblcustomers c, tblorders o, tblorderlines 
WHERE c.custstat = 'CA')
 
IF @B > 0 
BEGIN
            Print 'Number of titles that have been ordered from CA: ' + CAST (@B AS VARCHAR(64))
            Print 'something'
END 
ELSE 
 
       Print 'There are not any books ordered'

Open in new window

0
 
iolikeAuthor Commented:
Thanks Guru, it worked
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one out:
DECLARE @B int;
SET @B = (SELECT COUNT(*) 
FROM tblorders tb join tblorderlines ol on tb.ordernumber = ol.ordernumber
join tblcustomer cu on tb.custid = cu.custid
join Tblbooks bo on ol.bkid = bo.bkid;
WHERE custstat = 'CA');
 
IF @B > 0 
    Print 'Number of titles that have been ordered from CA: ' + @B;
ELSE 
    Print 'There are not any books ordered';

Open in new window

0
 
SharathData EngineerCommented:
rrjegan17 - you will get conversion failure error.use CAST or CONVERT to convert @B to varchar.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Sharath,
    SQL Server does an implicit conversion between int to varchar.

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
0
 
SharathData EngineerCommented:
here an int value is added (or appended) to a string. so you will get conversion failure.

DECLARE @B int;
set @B = 4;
 
IF @B > 0 
BEGIN
            Print 'Number of titles that have been ordered from CA: ' + @B 
            Print 'something'
END 
ELSE 
 
       Print 'There are not any books ordered'
 
/*
Result:
 
Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value 'Number of titles that have been ordered from CA: ' to data type int.
 
 
*/

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now