Can I use IIF statement in the RS query?

I tried to insert this line of code to the Column in the query design but it
doesn't work. Similar command works in MS-ACCESS.
IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE), 0)
Can someone help, thanks in advance.You can put this kind of formula in the textbox that displays the data. The
query design is limited to the back-end functionality, and Access has some
extra VB capabilities most databases don't support. But the reporting front
end does support this.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"LV" <LV@.discussions.microsoft.com> wrote in message
news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
>I tried to insert this line of code to the Column in the query design but
>it
> doesn't work. Similar command works in MS-ACCESS.
> IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> 0)
> Can someone help, thanks in advance.|||In addition to what Jeff says. What database are you going against? If it is
against Access MDB then you might be able to use the generic query window
(versus the graphical). This is basically passthrough window. It depends on
how the Access OLEDB provider handles it. If it is against SQL Server data
then this will definitely not work since it is not SQL Server SQL format. If
going against SQL Server then you can always test out your SQL using the
Query Analyzer.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> You can put this kind of formula in the textbox that displays the data.
The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting
front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>|||Thank you,
Yes I am using Access data, as you suggested I will try the textbox method.
"Jeff A. Stucker" wrote:
> You can put this kind of formula in the textbox that displays the data. The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>
>|||Thank you,
I did try the generic SQL but did not get the correct results somehow it
return just he true side of the IIF statement. Here is how I get around my
problem, I created the query with the IIF statemet in Access, on RS report I
connect to the query and it works. I don't know if this is the correct way
to do it but for now at least I can get it to work.
"Bruce L-C [MVP]" wrote:
> In addition to what Jeff says. What database are you going against? If it is
> against Access MDB then you might be able to use the generic query window
> (versus the graphical). This is basically passthrough window. It depends on
> how the Access OLEDB provider handles it. If it is against SQL Server data
> then this will definitely not work since it is not SQL Server SQL format. If
> going against SQL Server then you can always test out your SQL using the
> Query Analyzer.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > You can put this kind of formula in the textbox that displays the data.
> The
> > query design is limited to the back-end functionality, and Access has some
> > extra VB capabilities most databases don't support. But the reporting
> front
> > end does support this.
> >
> > Cheers,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "LV" <LV@.discussions.microsoft.com> wrote in message
> > news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> > >I tried to insert this line of code to the Column in the query design but
> > >it
> > > doesn't work. Similar command works in MS-ACCESS.
> > >
> > > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
> GETDATE),
> > > 0)
> > >
> > > Can someone help, thanks in advance.
> >
> >
>
>

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

Can I setup a Global Public View in SQL Server ?

I need a master view that gets data from different DBs located on the same SQL Server (similar with Oracle feature that allows to get data from different schemas).

Is it possible to create such a view ?

Thanks

Yes you can do it:

Create view my_view
as
select *
from
database1.schema3.table3
join database2.schema5.table6 on ....
union database3.schema5.table8

Keep in mind that if you are going to grant select privileges on this view to a specific user/role, you must grant same privileges on all underlyeing tables.

To make things simpler you can also create synonyms to that tables, like in Oracle, but you have to qualify whem with database names.

|||Thanks !

And this view can reside in any of the DBs, isn't it ?|||Yes, it can reside on any USER db, don't place it in master, tempdb, etc.