StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use:</p> <pre><code>SELECT TOP(@ArticleNo) </code></pre> <p>Therefore:</p> <pre><code>SELECT TOP(@ArticleNo) dbo.Sal_POSDet.ArtKey, dbo.ArtWithVendor.ArtCode, dbo.ArtWithVendor.ArtName, Sum(isnull(dbo.Sal_POSDet.Qty,0))as Pair, Sum(isnull(dbo.Sal_POSDet.Total,0)) as TurnOver into #temp FROM dbo.Sal_POS INNER JOIN dbo.Sal_POSDet ON dbo.Sal_POS.SalesKey = dbo.Sal_POSDet.SalesKey INNER JOIN dbo.ArtWithVendor ON dbo.Sal_POSDet.ArtKey = dbo.ArtWithVendor.ArtKey WHERE Sal_POS.IsHold=0 and Sal_POS.SalesDate between @FromDate and @ToDate and CASE WHEN @ArtKey is null THEN 1 WHEN ArtWithVendor.ArtKey =@ArtKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtCatKey is null THEN 1 WHEN ArtWithVendor.ArtCatKey =@ArtCatKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtGroupKey is null THEN 1 WHEN ArtWithVendor.ArtGroupKey = @ArtGroupKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtTypeKey is null THEN 1 WHEN ArtWithVendor.ArtTypeKey = @ArtTypeKey THEN 1 ELSE 0 END = 1 and CASE WHEN @MaterialKey is null THEN 1 WHEN ArtWithVendor.MaterialKey = @MaterialKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ColorKey is null THEN 1 WHEN ArtWithVendor.ColorKey = @ColorKey THEN 1 ELSE 0 END = 1 and CASE WHEN @VendorKey is null THEN 1 WHEN ArtWithVendor.VendorKey = @VendorKey THEN 1 ELSE 0 END = 1 and CASE WHEN @VendorTypeKey is null THEN 1 WHEN ArtWithVendor.VendorTypeKey = @VendorTypeKey THEN 1 ELSE 0 END = 1 and CASE WHEN @OutLetKey is null THEN 1 WHEN Sal_POS.OutLetKey = @OutLetKey THEN 1 ELSE 0 END = 1 Group by dbo.Sal_POSDet.ArtKey, dbo.ArtWithVendor.ArtCode, dbo.ArtWithVendor.ArtName </code></pre> <hr> <p>Alternatively, add the following before your <code>SELECT</code> query:</p> <pre><code>IF @ArticleNo IS NOT NULL BEGIN SET ROWCOUNT @ArticleNo END </code></pre> <p>Then after your <code>SELECT</code> query you need to reset the <code>ROWCOUNT</code> by doing:</p> <pre><code>IF @ArticleNo IS NOT NULL BEGIN SET ROWCOUNT 0 END </code></pre> <hr> <p>Therefore, overall it will be something like:</p> <pre><code> IF @ArticleNo IS NOT NULL BEGIN SET ROWCOUNT @ArticleNo END SELECT dbo.Sal_POSDet.ArtKey, dbo.ArtWithVendor.ArtCode, dbo.ArtWithVendor.ArtName, Sum(isnull(dbo.Sal_POSDet.Qty,0))as Pair, Sum(isnull(dbo.Sal_POSDet.Total,0)) as TurnOver into #temp FROM dbo.Sal_POS INNER JOIN dbo.Sal_POSDet ON dbo.Sal_POS.SalesKey = dbo.Sal_POSDet.SalesKey INNER JOIN dbo.ArtWithVendor ON dbo.Sal_POSDet.ArtKey = dbo.ArtWithVendor.ArtKey WHERE Sal_POS.IsHold=0 and Sal_POS.SalesDate between @FromDate and @ToDate and CASE WHEN @ArtKey is null THEN 1 WHEN ArtWithVendor.ArtKey =@ArtKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtCatKey is null THEN 1 WHEN ArtWithVendor.ArtCatKey =@ArtCatKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtGroupKey is null THEN 1 WHEN ArtWithVendor.ArtGroupKey = @ArtGroupKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ArtTypeKey is null THEN 1 WHEN ArtWithVendor.ArtTypeKey = @ArtTypeKey THEN 1 ELSE 0 END = 1 and CASE WHEN @MaterialKey is null THEN 1 WHEN ArtWithVendor.MaterialKey = @MaterialKey THEN 1 ELSE 0 END = 1 and CASE WHEN @ColorKey is null THEN 1 WHEN ArtWithVendor.ColorKey = @ColorKey THEN 1 ELSE 0 END = 1 and CASE WHEN @VendorKey is null THEN 1 WHEN ArtWithVendor.VendorKey = @VendorKey THEN 1 ELSE 0 END = 1 and CASE WHEN @VendorTypeKey is null THEN 1 WHEN ArtWithVendor.VendorTypeKey = @VendorTypeKey THEN 1 ELSE 0 END = 1 and CASE WHEN @OutLetKey is null THEN 1 WHEN Sal_POS.OutLetKey = @OutLetKey THEN 1 ELSE 0 END = 1 Group by dbo.Sal_POSDet.ArtKey, dbo.ArtWithVendor.ArtCode, dbo.ArtWithVendor.ArtName IF @ArticleNo IS NOT NULL BEGIN SET ROWCOUNT 0 END </code></pre> <p>However using <code>ROWCOUNT</code> is not ideal as it will mess with your sub-query results.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload