Need help converting a select query into a case statement

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
LauraConvert a SELECT statement into a CASE statement?

I haven't a clue what you are intending to do.|||This is the query that I am dealing with:

SELECT PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber,
CASE WHEN (AR.InvoiceDate = '')
THEN '0'
ELSE DATEDIFF(day, AR.InvoiceDate, ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld,
SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND
LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage,
SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
FROM PR LEFT OUTER JOIN
AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND
AR.Invoice = LedgerAR.Invoice LEFT JOIN
LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
CFGDates ON CFGDates.Period = AR.Period
WHERE (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND
(AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
(LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND
(LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
HAVING SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0

Within the DateDiff function I need to find the end of the period using a case statment such as

Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod

The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

Hope that clears things up.

Thanks,
Laura|||that's right, you cannot assign an alias to a value if that value is inside a function

just drop the As EndPeriod

oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero|||Here is a CASE statement that incorporates your logic:

CASE WHEN (AR.InvoiceDate = '') THEN '0'
WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
ELSE DATEDIFF(day, AR.InvoiceDate, 0)
END AS DaysOld

...but whether it will integrate with the rest of your SQL statement, I do not know.|||blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

that date equal to an empty string gives me the creeps

and the quotes around the zero don't make me feel too hot either

also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh|||Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

What gives me the heebie-jeebies about it is all the hard-coded values.

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

Need help ASAP with calendar datetime popup

I have a report created in ssrs. In the sql t script I used the between @.startdate and @.enddate statement for a parameter. In my report parameters dropdown I selected the timedate as my datatype. The report works fine if view it from my developement server the calander loads ok. When I view it from any other system the calender Icon is present but with not work. Any suggestions. Is there anything that needs to be put into the webconfig file to make this work. Javascript??

Hi,

Post your code. Someone will have a look at it.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Need Help - I Have Select Distict Problems

I'm having an issue with the following sql statement:

1SELECT Property.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice,2 Media.ThumbView3FROM PropertyLEFTOUTER JOIN4 MediaON Property.PropertyID = Media.PropertyID

I'd like to be able to select by the distinct Media.PropertyID instead of the Property.PropertyID.

Hi,

would you like to select only distinct Media.PropertyID only or distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView?

either way, just including a distinct keyword infront of your select query would be enough:

select distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView from ....

if propertyID is a repeating field you can use the GROUP BY clause to get what you want...

select Media.PropertyID, max(Property.ListingEndDate), max(Property.ListingName), max(Property.StreetAddress), max(Property.City) ...
from ...
GROUP BY Media.PropertyID

Hope this helps...

|||

I don't understand what I'm doing wrong with these select statements.

1SELECT2 Property.PropertyID,3COUNT(Property.PropertyID)AS PropertyCount,4SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace5FROM6 Property7INNERJOIN8 UnitsON Property.PropertyID = Units.PropertyID9GROUP BY10 Property.PropertyID

I get back:

PropertyID = 48

PropertyCount = 4<-- Not what I'm expect. I'm looking for: PropertyCount = 1

TotalSpace = 12500

What am I doing wrong here? 

|||

I take it I should have used a sub select statement.

- lot's to learn...

|||

eterry28:

I take it I should have used a sub select statement.

Hi,

im assuming you want only rows where PropertyCount = 1
you can simply use the HAVING clause:

SELECT
Property.PropertyID,
COUNT(Property.PropertyID)AS PropertyCount,
SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace
FROM
Property
INNERJOIN
UnitsON Property.PropertyID = Units.PropertyID
GROUP BY
Property.PropertyID
HAVING
COUNT(Property.PropertyID) = 1

Hope this helps...

Need Dynamic SQL Select turotrial

Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.

Thank you

Do you mean full text searching? (Searching for a particular word within a lot of text)

|||

I have a database column 'Description' varchar(600) that contains product descriptions. I need to search it looking for any of the keywords contained in a Keyword string.

Example Keyword string - sail boat hardware

I need it to find all rows WHERE 'sail' or 'boat' or 'hardware' is found.

Thank you

|||

That form of search is more a full text search rather than a dynamic search. The latter is searching on one to many columns as selected at runtime.

|||

I am using a shared SQL2000 server. What I want to do is find a way in ASP.Net to take the keyword string entered and convert it into a Sql2000 select statement like below.

Keywords entered: sail boat hardware

SELECT Company, Phone, Description
FROM tblCompanyInfo
WHERE (tbl.CompanyInfo.Description LIKE '%' + "sail" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "boat" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "hardware" + '%')

|||

Look athttp://msdn2.microsoft.com/en-gb/sql/aa336289.aspx

|||

I am actualy looking for an ASP routine that splits the Keyword string and creates the OR elements for a Sql2000 compatible Sql SELECT command. I have looked at the many CONTAINS options discussed in the article but did not find any that offer a split like solution. As for a full text search, that option is not available at least not in the shared SQL environment that I am using.

Thank you

|||

In which case you need to roll your own text search solutions.

Create a Noise words table to contain words that you do not want indexed.

|||

Looks like someone should create a new ASP.Net SearchControl. A bunch of us out here could sure use it.

Thank you for the follow up.

sql

need advice on Update statement

I have a simple update statement that set sthe student's password to their id number:

update studemo set passwd = ident

ident is not part of the primary key but there is a unique, non-clustered index using that column

This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?

If that is your exact statement, then what you aredoing is updating the password to ident for every row. I assume that that is the purpose, right?

If so, then the next question is a matter of hardware. Is this on a small machine, or a server class machine.

And, is the table clustered on password? ident being clustered wouldn't matter, but passwd would. Either way, if this is something you are doing "offline" then drop or disable indexes (2005 only) and you will certainly get better performance.

Basically, what I am trying to say is that there is no way to know from what you have told us so far, but you certainly do need to look at this being hardware oriented, so check perfmon and see what is being taxed.

|||SQL Server (2005) is on a server class machine. Hardware resources should not be an issue but I'll look into it.

The are no indexes using the password column. I'll check perfmon.|||

Add a loop logic and commit after every 1000 rows

The trick is to add a flag to the table (call it anything like UPDATE_FLAG , set it to some value and in where clause do a not equal to the same value :

SET ROWCOUNT 1000

UPDATE
TABLE1
SET
COLUMN = WHATEVER

, UPDATE_FLAG = '1'
FROM

TABLE2 (OR) WHATEVER
WHERE
UPDATE_FLAG != '1'

|||

You can perform the update in batches like below to reduce resource usage:

while(1=1)

begin

-- Update 500 rows at a time

update top(500) studemo set passwd = ident

if @.@.rowcount = 0 break

end

|||

Are your logs and data on the same drive? That could be a problem, especially if the rows are really big, and even worse if the value you are updating the value to is bigger than what it was.

I also should have mentioned to look for blocking :)

In perfmon I would look for large quantities of disk queuing and/or memory stress. Then implement one of the batching mechanisms (and probably drop indexes might help. This is esepecially true when you have large rows as page splits could be very frequent.) On such a small table building the indexes might take less time than maintaining them.

|||The log and data are on separate drives.

When I run the script from above:

while(1=1)

begin

-- Update 500 rows at a time

update top(500) studemo set passwd = ident

if @.@.rowcount = 0 break

end

I would have expect to see approximately 50 rows indicating 500 rows

had been updated since the table only has 25K rows. Before I cancelled

the update, I had more than 1000 messages that 500 rows had been

updated.

Weird ... to me.

Thanks for all the reponses so far.

|||What messages did you see? Does the table have any triggers for update? What does (select count(*) from studemo) return?|||Count = 25704.

Message: (500 row(s) affected) tr_slvswstuU fired, no change to address fields

There is one trigger that fires:

******************************************************

USE [fakeSISdata]
GO
/****** Object: Trigger [dbo].[tr_slvswstuU] Script Date: 08/30/2006 14:03:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[tr_slvswstuU] ON [dbo].[studemo]
FOR UPDATE
AS

set nocount on
--print 'tr_slvswstuU fired'

declare @.hcheck smallint,
@.mcheck smallint,
@.pcheck smallint,
@.ppass smallint

set @.hcheck = 0
set @.mcheck = 0
set @.pcheck = 0

Check for changes is Physical Address--
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.homeaddr1 <> d.homeaddr1 or
i.homeaddr2 <> d.homeaddr2 or
i.homecity <> d.homecity or
i.homezip <> d.homezip))
set @.hcheck = 1
--select @.hcheck
--Check for changes in Mailing Address-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.mailaddr1 <> d.mailaddr1 or
i.mailaddr2 <> d.mailaddr2 or
i.mailcity <> d.mailcity or
i.mailzip <> d.mailzip))
set @.mcheck = 1

--Check for changes in Phone Number or Phone Type-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.phnnumber <> d.phnnumber or
i.phntypec <> d.phntypec or
i.phnunlist <> d.phnunlist or
i.phnmsg <> d.phnmsg))
begin

declare @.oldphone char(10),
@.newphone char(10),
@.unlist smallint,
@.message smallint

set @.pcheck = 1
set @.oldphone = (select phnnumber from deleted)
set @.newphone = (select phnnumber from inserted)
set @.unlist = (select phnunlist from inserted)
set @.message = (select phnmsg from inserted)

--if @.oldphone is null or @.oldphone = ''
--set @.oldphone = 0

if exists(select phnnumber from inserted where (phnnumber is not null or phnnumber <> '') and phntypec = 'R')
set @.ppass = 1

if exists(select i.phnnumber from inserted i,deleted d
where i.suniq = d.suniq and
((i.phnnumber is null or i.phnnumber = '') and
(d.phnnumber is not null or d.phnnumber <> '') or added or
(d.phntypec = 'R' and i.phntypec <> 'R')))
begin
if exists(select phnnumber from deleted where phntypec = 'R')
set @.ppass = 2
end
end

-Call to siSp_syncaddr
--select @.hcheck,@.mcheck,@.pcheck
if @.hcheck = 1 or @.mcheck = 1 or @.pcheck = 1
begin
declare @.ident varchar(10)
set @.ident = (select ident from inserted)

if exists(select * from stucon where ident = @.ident and conmail11 = 1)
begin
--Call for multiple contacts--
if exists(select ident,count(*) from stucon where ident = @.ident and conmail11 = 1
group by ident having count(*) > 1)
begin
print 'tr_slvswstuU fired, called siSp_syncaddr for multiple contacts'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
--exec siSp_syncaddr @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
else
begin
--Call for sinlge contact-
declare @.conuniq int
set @.conuniq = (select conuniq from stucon where ident = @.ident and conmail11 = 1)

print 'tr_slvswstuU fired, called siSp_syncaddr for single contact'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
exec siSp_syncaddr @.ident, @.conuniq, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
end
else
print 'tr_slvswstuU fired no stucon records where conmail11 = 1'

end
else
print 'tr_slvswstuU fired, no change to address fields'

set nocount off
**********************************************************************|||You will have to check the trigger logic. There are lot of SPs that are getting called from trigger code. It is not clear what they are doing. They could probably be adding rows to the same table. In any case, the UPDATE should affect same number of rows as those in the table if there are no new rows being added in the trigger. You can verify by accumulating the @.@.ROWCOUNT value in the batched update code and checking it at the end. You can use SET NOCOUNT ON to suppress the messages.

Need advice on IF...THEN...ELSE type T-SQL syntax

I don't write SQL that often, so...

What is the most "efficient" way to write an SQL statement that accomplishes:

If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information.

Thanks in advance.

Look into If Exists.

|||

try this

ifexists(SELECT key_fieldfrom yourtablewhere key_field='key')BEGINUPDATE yourtableset [thefield]='your new value'WHERE key_field='key'END

|||

My understanding is that with the 2008 release of sql server, it will add the MERGE command, which is designed precisely for this purpose.

Otherwise, you'll have to use the prior example (but remember to add an "else" branch to do the insert.

Alternatively, there is a way to do it as two statements (one update, one insert) that will handle all the rows you need updated/inserted.

It just depends upon the nature of the update. If all the records to be updated need to be updated with the same info (i.e., add 5% to the salary), one update statement will update all of them.

Then, do an insert statement in the form of:

insert into destination_table (col1,col2) select colA, colB from source_table where source_table.key_column not in (select key_column from destination_table)

That will insert all the missing records.

|||

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

|||

DisturbedBuddha:

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

Duh! Much easier and probably more efficient than the exists check.

|||

http://disturbedbuddha.wordpress.com/2007/11/29/easy-sql-if-record-exists-update-it-if-not-insert-it/

need a single sql statement

How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

1st query
Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

2nd query
Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2The simplest would be to cut and paste, but you'll have to verify the accuracy of the final result:

Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value
inner join (Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2) x
on table1.joinfield1=x.joinfield1
and table1.joinfield2=x.joinfield2|||your question is a bit hard to understand, but it looks like you want those two queries left joined

that's easy --select curly.*
, larry.*
from (
select field1
, field2
, joinfield1
, joinfield2
from Table1
inner
join Table2
on Table1.field3
= Table2.field4
where field5 = Value
) as curly
left outer
join (
select sum(agfield1) as f1
, sum(agfield2) as f2
, joinfield1
, joinfield2
from Table3
inner
join Table4
on Table3.agfield3
= Table4.agfield4
where agfield5 = Value
group
by joinfield1
, joinfield2
) as larry
on curly.joinfield1 = larry.joinfield1
and curly.joinfield2 = larry.joinfield2note i fixed an apparent typo|||thanks, you really understood exactly what I wanted.
The sql statement worked fine.

Thanks very much for your help.
Best Regards,
Manolis Perrakis

Need a good insert statement

Hi, I need some help. I've spent nearly a w of work and lunch time trying
to crack this nut, and I guess I am finally stuck.
I have a table with financial data like invoices. The tables are huge.
Hundreds of millions of rows per year.
I want to store some additional information related to gambling in one of
these tables.
I want to put this new data way off in the corner of the table temporarily
until I am able to come back to work.
For reasons that are private, I do not want to create a new table.
So if I could stick this information out starting at say row 104,000 and
column 333,000 that would be great. No one's going to look there. And if
they do it will look like some kind of mistake. I can't figure out how to
get the data there.
Any good Ideas would naturally be of great help. Also, since I am in sort of
a hurry (tax day is april 15) I would really appreciate it if I could just
get some of those "only the quality answers." I know a lot of people like to
yik yak, but I just need da facts. Thanks a lot!Hi
I'm not sure I understand you. Do you want to add additional column? If you
do , use ALTER TABLE TableName ADD Column.....
If you want to do paging I'd recomend you vist Aaron's web site
www.aspfaq.com .
"TargetBleigh" <ddsd*f.com> wrote in message
news:2c6dnRzmUNP2ec3fRVn-1g@.comcast.com...
> Hi, I need some help. I've spent nearly a w of work and lunch time
trying
> to crack this nut, and I guess I am finally stuck.
> I have a table with financial data like invoices. The tables are huge.
> Hundreds of millions of rows per year.
> I want to store some additional information related to gambling in one of
> these tables.
> I want to put this new data way off in the corner of the table temporarily
> until I am able to come back to work.
> For reasons that are private, I do not want to create a new table.
> So if I could stick this information out starting at say row 104,000 and
> column 333,000 that would be great. No one's going to look there. And if
> they do it will look like some kind of mistake. I can't figure out how to
> get the data there.
> Any good Ideas would naturally be of great help. Also, since I am in sort
of
> a hurry (tax day is april 15) I would really appreciate it if I could just
> get some of those "only the quality answers." I know a lot of people like
to
> yik yak, but I just need da facts. Thanks a lot!
>

Need 2nd set of eyes on a CASE statement

I am going batty looking at this! I keep getting ORA-00907:Missing Right parenthesis when I have the 2nd case statement in this SQL (in bold). If I remove that line, it works just fine. As I don't see any left ( w/o a right ), I have no idea what the real issue is. (this is against a Oracle DB)

select rownum as IndexNum,
lm.hostlocid "Loc. Code",
pm.hostpartid "Comcode",
pm.partname "Description",
pc.hostplannercodeid "Plnr Code",
pc.codedescription "PC Descr",
sa.ohneworig "On Hand New Orig",
sa.ohfixedorig "On Hand Fixed Orig",
sa.ohbadorig "On Hand Bad Orig",
sa.onorderorig "On Order Orig",
sa.backorderorig "Backorder Orig",
sa.inrepairorig "In Repair Orig",
sa.inreturnorig "In Return Orig",
sa.allocatedorig "Allocated Orig",
sa.amountcustom1 "Amt Cus 1",
sa.amountcustom2 "Amt Cus 2",
sa.amountcustom3 "Amt Cus 3",
sa.amountcustom4 "Amt Cus 4",
sa.amountcustom5 "Amt Cus 5",
upper(ip.isaslitem) "Is ASL Item",
upper(ip.aslstatus) "ASL Status",
ip.stockmax "Stock Max",
ip.rop "Reorder Point",
pm.price "Std Cost",
fd.ForecastAmount,
to_char(fd.ForecastSliceDate,'MM-YYYY') "Current Period",
case when (sa.ohfixedorig + sa.ohneworig)=0 or fd.ForecastAmount=0 then null else round(((sa.ohfixedorig + sa.ohneworig)/(fd.ForecastAmount/30))) end Out_Of_Stock_Days,
pcd.ChainParentID,
pcd.RelationType,
case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
from
stock_amount sa,
part_master pm,
loc_master lm,
loc_type lt,
stock_level ip,
planner_codes pc,
Forecasted_data fd,
part_chain_details pcd
where
sa.partid=pm.partid and
sa.locid=lm.locid and
lm.loctypeid=lt.loctypeid and
ip.partid=sa.partid and
pm.plannercodeid=pc.PLANNERCODEID and
ip.locid=sa.locid and
fd.partid = sa.partid and
fd.locid = sa.locid and
lm.hostlocid = 'B014' and
pcd.partid (+) = pm.partid and
(pc.hostplannercodeid = 'C50' or pc.hostplannercodeid = 'C51' or pc.hostplannercodeid = 'C52' or pc.hostplannercodeid = 'ZZ5') and
fd.ForecastSliceDate >ADD_MONTHS(SYSDATE,-1) and
fd.ForecastSliceDate<ADD_MONTHS(SYSDATE,0)

Any help showing the evil of my ways would be greatly appreciated!case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type

Your problem, is that both (N'Alt') and ('N'Rep') are invalid. The syntax to append text to a column is, (column||' text');|||try it without the Ns

Edit: there's no concatenation; ChainType is a column alias|||Thanks R123456 and R937.

For some reason I had in my head that when using a case statement to output a text value I had to preced the 'text' with (N'my text').

No I was not trying to append to a column! Big OOPS!

I decided to try a DECODE statement that works. (I also was not taking into account nulls!)

With decode( pcd.relationtype,1,'Alt',0,'Rep','') as Chain_Type, the output is what I expect.

Hopefully I can be back to the dynamic SQL issue I posted earler this week!

Thanks for the slap in the face to wake me up! :-)