Query, help with where clause to reduce resultset

I need some help with the @.LanguageId part of this query to exclude/include
rows to be returned.
ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
'nn'.
If rows with the specified @.LanguageId exists, then I want only those rows,
otherwise I want only the 'nn' rows (a fallback mechanism).
Now the query returns all the rows for both the specified @.LanguageId and
those with @.LanguageId = 'nn'. Any ideas?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
/tedHow about this?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
@.LanguageId END
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||I'm afraid it will not solve the problem. @.LanguageId will always be
something as input parameter. The clue is that I want the 'nn' rows if the
specified 'dk' rows does not exist in the database.
/ted :)
"Pierre Albisser" <pierre.no_spam@.albisser.de> skrev i melding
news:%23079C5GXFHA.4032@.tk2msftngp13.phx.gbl...
> How about this?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
> @.LanguageId END
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news|||Try,
if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageId = @.LanguageId)
else
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageid = 'NN')
AMB
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Hi Ted
Probable you can try this way
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = ISNULL(@.LanguageId,'NN')
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Thanks :) It seems to be a way to go.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> skrev i melding
news:2E7D1FD3-54AC-4A40-87E4-8EA9EEFF5F6F@.microsoft.com...
> Try,
> if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
> CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
> CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageId = @.LanguageId)
> else
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageid = 'NN')
>
> AMB
> "ted" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Yoiu might also want to start following proper data modeling
conventions and stop using prefixes that tell us HOW something is used
in one table, and come with names that tell us what something is in the
whole data model. A prefix that tells us something is a table in a
language that hsa only one data strucutre is absurd.
See ISO-11179 for the standards. Can I assume that you meant to use
the ISO language codes and not a identifier?|||On Thu, 19 May 2005 14:19:58 +0200, ted wrote:

>I need some help with the @.LanguageId part of this query to exclude/include
>rows to be returned.
>ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
>'nn'.
>If rows with the specified @.LanguageId exists, then I want only those rows,
>otherwise I want only the 'nn' rows (a fallback mechanism).
>Now the query returns all the rows for both the specified @.LanguageId and
>those with @.LanguageId = 'nn'. Any ideas?
>SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
>FROM ctbl_content_rel CONTENTREL
>JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
>WHERE CONTENT.pk_content = @.ContentId
>AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
>/ted
>
Hi ted,
If you're not afraid of using proprietary SQL that won't port to other
databases, you can use this:
SELECT TOP 1 CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid =
'NN')
ORDER BY CASE WHEN CONTENTREL.languageid = 'NN' THEN 2 ELSE 1 END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query String Programmatically

Is any way to Modified The Query String Programmatically for a Report ?
I want to be able to Change the WHERE Clause without using Parameter..
e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
want something like WHERE DeliveryDate < '2005-04-04'.
Thank. Any Idea I would appreciated.
--
Message posted via http://www.sqlmonster.comGot from BOL.
Passing a Report Parameter on a URL
You can pass report parameters to a report by including them in a URL. These
URL parameters are not prefixed, because they are passed directly to the
report processing engine. For more about report parameters, see Running a
Parameterized Report.
Example
The following example uses the report parameter EmployeeID to render the
specified report:
http://server/reportserver?/Sales/Northwest/Employee Sales
Report&rs:Command=Render&EmployeeID=1234See Also
The operator cant be changed unless you use dynamic SQL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Edgar Mantilla via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:000edb152d5c494b8854daa6aaf009ff@.SQLMonster.com...
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report
> I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Thank you,
I am working in a windows form that selects the report from the Report
Server and send it as .PDF to a specific location, Passing a Report
Parameter on a URL, it works well, but I want to have more flexibility in
building my query string, so I worked with a RDL Generator (Form BOL
Walkthrough ? Generating RDL Using the .NET Framework), which work fine
when you are creating a report from scratch.
Is any way to change the CommandTex or Query string of an existing report
programmatically?
--
Message posted via http://www.sqlmonster.com|||Edgar,
AFAIK you can't change the query text at report run time. There are
solutions to this type of complex querying, and let me say up front that I
recommend you put your query in a stored procedure instead of trying to put
this all into the report itself.
As Jens said, you can use dynamic SQL. This involves building your query as
a string and then calling EXEC (string). If you only have a limited number
of choices (e.g. only <, =, or >) then you can simply use IF statements and
hard code the queries for each branch.
Note: if you go the dynamic SQL route, beware of SQL injection attacks.
That's a whole subject, and too much to discuss here in one post.
Ted
"Edgar Mantilla via SQLMonster.com" wrote:
> Thank you,
> I am working in a windows form that selects the report from the Report
> Server and send it as .PDF to a specific location, Passing a Report
> Parameter on a URL, it works well, but I want to have more flexibility in
> building my query string, so I worked with a RDL Generator (Form BOL
> Walkthrough â' Generating RDL Using the .NET Framework), which work fine
> when you are creating a report from scratch.
> Is any way to change the CommandTex or Query string of an existing report
> programmatically?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi, in the Generic Query Designer you can use some like this:
="SELECT " & Parameters!Field1.Value
" FROM " &
Parameters!Table.Value
"Edgar Mantilla via SQLMonster.com" wrote:
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com
>