Solved

Nested Query Problem

Posted on 2009-05-05
3
242 Views
Last Modified: 2012-05-06
The nested COUNT query does not recognize a.CustomerID, it comes up asking for that. What am I doing wrong?
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType) 
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity, b.NumberOfOtherItems, a.DebtType 
FROM ltblAllItems a, (SELECT COUNT(*) - 1 AS NumberOfOtherItems FROM ltblAllItems c WHERE a.CustomerID = c.CustomerID) AS b 
WHERE a.Balance = 0

Open in new window

0
Comment
Question by:bejhan
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 24309618
change to this:
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType) 
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity
, (SELECT COUNT(*) - 1 AS NumberOfOtherItems FROM ltblAllItems c WHERE a.CustomerID = c.CustomerID) 
, a.DebtType 
FROM ltblAllItems a
WHERE a.Balance = 0

Open in new window

0
 
LVL 1

Author Comment

by:bejhan
ID: 24309635
Nice I didn't know you could just do that. Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24309654
you could have done like this:
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType) 
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity
, b.NumberOfOtherItems 
, a.DebtType 
FROM ltblAllItems a
INNER JOIN ( SELECT CustomerID, COUNT(*) - 1 AS NumberOfOtherItems 
              FROM ltblAllItems c 
            GROUP BY CustomerID 
           ) b
  ON (a.CustomerID = c.CustomerID)
WHERE a.Balance = 0

Open in new window

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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