Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts

Monday, March 26, 2012

I would propose the following workaround ...

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile|||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Friday, March 23, 2012

I want to write an SQL statement which returns matching values but ignores the first 2 dig

I want to write a statement something like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)

But i want the search to ignore the first 2 digits so that it will return e.g

10/11/1234, 09/11/1234 so that it's only matching the last part

Any Help Would be greatly appreciated Thanks

try use Substring in SQL statement|||

I'm not sure if you want this to just a straight up query or something dynamic

If you want it to be passed into a query, you can do
string criteria = "11/11/1234";
"SELECT ... (File_No = " + criteria.Remove( 0, 2) + ");

Use Parameterized query and not the exact example above.

If you want just a straight up query use LIKE
SELECT... (File_No LIKE '%/11/1234')

|||

You can use something like this:

DECLARE

@.lcModifiedIDvarchar(10),

@.liMaxFiedlLength

asint,

@.liStartPoint

asint

SET

@.liMaxFiedlLength= 100

SET

@.liStartPoint= 4

SET

@.lcModifiedID=substring('11/11/1234/',@.liStartPoint,@.liMaxFiedlLength)

print

@.lcModifiedID

SELECT

Add_Date, File_NoFROM(SELECT'12/11/1234/' File_No,'aa'Add_date)aaWHEREsubstring(File_No,@.liStartPoint,@.liMaxFiedlLength)= @.lcModifiedID

Thanks

JPazgier

|||

I Have a textBox named TextFile which is where the user enters the file number which will be in the format of 11/11/1234 and then there is a button with an on click event to trigger my SQL query

I have an SqlDataAdapter with the first parameter set as @.FileNo

I want the query based entirely on the the value of textBox


I am currently doing it like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = @.File_No)

But this only returns exact matches and as i say i need to return values that ignore the first 2 digits contained in @.File_No

Thanks for such a quick response

|||

I am pretty sure you can change "File_No = @.File_No" to "File_No LIKE @.File_No"

Then when you declare the value of the parameter:
.Value = "%" + TextFile.Text.Remove( 0, 2);

|||

Yes, you are right maybe it will work but remember that LIKE structure is very slow and designed for another purposes.

Thanks

JPazgier

Friday, March 9, 2012

I need to return all the recent ImportId Data

Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns 2550 rows of data.

In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns 2500. I need help retirenving the entire liste with the most recent OrbitimportId.

Help!

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetAttribute.AssetDescription, dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.ESNTracking.ReportTime, dbo.ESNTracking.CurrLocStreet, dbo.ESNTracking.CurrLocCity, dbo.ESNTracking.CurrLocState,
dbo.ESNTracking.CurrLocZip, dbo.ESNTracking.CurrLocCounty, dbo.ESN.EsnNumber, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.MapUrl,
dbo.InventoryOrigin.WarehouseDescription
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId LEFT OUTER JOIN
dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportId
WHERE (dbo.ESNTracking.OrbitImportId =
(SELECT MAX(OrbitImportId) AS Expr1
FROM dbo.ESNTracking AS ESNTracking_1))
ORDER BY dbo.AssetType.Description

danjaman:

Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns2550rows of data.

In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns2500. I need help retirenving the entire liste with the most recent OrbitimportId.

Did you mean the query missed 50(2550-2500) rows?

Friday, February 24, 2012

I need Help.

Hi. I'm trying a SQL like this
SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
its returns me
PRICE
100
150
30
But i lik SQL like this
SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
i want return like
PRICE
280
When i runs second SQL an error happens.
" Cannot perform an aggregate function on an expression containing an
aggregate or a subquery. "
_____________________________________________________________
Murat FÝDANOne way
SELECT SUM(MinPrice)AS SumPrice
FROM (SELECT MIN(PRICE) AS MinPrice
FROM TABLE1
WHERE ID=3D112
AND STATUS=3D1
GROUP BY ID )x
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Aug 23, 9:04 am, "Murat Fidan" <murat.fi...@.tekdurak.com> wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY= ID
> i want return like
> PRICE
> 280
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
> _____________________________________________________________
> Murat F=DDDAN|||Murat Fidan,
Use a derived table.
select sum(min_price)
from
(
SELECT MIN(PRICE) as min_price
FROM TABLE1
WHERE ID=112 AND STATUS=1
GROUP BY ID
) as t
go
AMB
"Murat Fidan" wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> i want return like
> PRICE
> 280
>
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
>
> _____________________________________________________________
> Murat FÃ?DAN
>
>

Sunday, February 19, 2012

I need help with a Query and Count

I have a pretty simple query:

Select title, avg(score) from votes group by title order by avg(score) Desc

This returns a result like this:

Abbey Road 4.0
The White Album 3.5
Meet the Beatles 3.0

The values in the score field are always 1, 2, 3, 4, or 5

What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this.

Abbey Road 4.0 0 0 1 1 1
The White Album 3.5 0 1 0 0 1
Meet the Beatles 3.0 2 2 2 2 2

I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.

Any suggestions would be greatly appreciated,

Chris

I found a solution using sum and case.

Chris