Easy way to copy over calculated fields to another dataset

I have a dataset in which I created about 10 calculated fields. I
often use this dataset for other reports. How can I copy over those
fields to the dataset I create in my other reports quickly?Hi.
Edit the code (RDL) of the report, look for the dataset fields, copy
and paste them onto the code of the new report.
Regards,
Paulo Cunhasql

Easy to Say but hard to implement

I have a table named Holding_Value that has several fields in it among
which are UID, fkHolding, EffDate, Units, MarketValue, AssetPrice. UID
is an identity field and fkHolding is a foreign key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table.
what i'm trying to do is get all the values (fkHolding, Effdate, Units,
MarketValue) for all fkHolding for a specific date. That would be
pretty easy if there each unique fkHolding had a corresponding value
for every date. The exception is that if no date is found than you
would have to get the next date less then or equal ot the query date.
To furhter explain assume that there 100 records in the table and there
are only 10 distinct fkHolding values. My result will need to include
only 10 records. Each record will have the values of the row containing
the values less than or equal to the given date for a specific given
date. so if given date (EffDate) is 12/1/2004 and 5 of the 10 distinct
fkHolding have been priced on that date, than we get those values, the
rest 5 rows in the resultset need to be the values of of the latest
date less than the given date.

Now the second problem is that this needs to be efficient because this
is only a part of my subquery and the table does not have 100 records
but a few million records. Now what i can do is get the latest value if
i were given an fkHolding for example i would write

declare @.fkHolding as integer
declare @.DateValue as datetime
select @.fkHolding = 2981
select @.DateValue = '9/2/2004'

select Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding, Holding_Values.AssetPrice,
Holding_Values.MarketValue
from Holding_Values INNER JOIN
(select max(Holding_Values.effdate) as DatePriced from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @.fkHolding and
Holding_Values.EffDate < @.DateValue) as a
on a.UID = Holding_values.UID ) as b
on Holding_Values.EffDate = b.DatePriced and Holding_Values.fkHolding =
@.fkHolding

or also would write it in the same way taking a different approach:

declare @.fkHolding as integer
declare @.DateValue as datetime
select @.fkHolding = 2981
select @.DateValue = '9/2/2004'

select Top 1 Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding,
Holding_Values.AssetPrice, Holding_Values.MarketValue from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @.fkHolding and
Holding_Values.EffDate < @.DateValue) as a
on a.UID = Holding_values.UID
Order by Holding_Values.EffDate desc

Both these queries produce a row each when ran for a specific date and
fkHolding. Now the challege is to get all the latest distinct fkHolding
values given only a date.
Thank you for your time and help.

Gent MetajOn 15 Dec 2004 12:07:48 -0800, Gent wrote:

(snip)
>what i'm trying to do is get all the values (fkHolding, Effdate, Units,
>MarketValue) for all fkHolding for a specific date. That would be
>pretty easy if there each unique fkHolding had a corresponding value
>for every date. The exception is that if no date is found than you
>would have to get the next date less then or equal ot the query date.
(snip)

Hi Gent,

Since you didn't post CREATE TABLE and INSERT statements to recreate your
situation, I couldn't test it, but something like this should do the
trick:

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
WHERE h.EffDate <= @.DateValue
AND NOT EXISTS (SELECT *
FROM Holding_Values AS h2
WHERE h2.EffDate <= @.DateValue
AND h2.EffDate > h.EffDate)

Another way to do it (test them both to see which one gives you the best
performance):

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
INNER JOIN (SELECT fkHolding, MAX(EffDate) AS EffDate
FROM Holding_Values
WHERE EffDate <= @.DateValue
GROUP BY fkHolding) AS h2
ON h.fkHolding = h2.fkHolding
AND h.EffDate = h2.EffDate

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||It would have been a lot easier to say if you'd posted some DDL, sample data
and expected results.
http://www.aspfaq.com/etiquette.asp?id=5006

What are the key(s)? Please include them with CREATE TABLE statements. Keys
are going to make a big difference to any query. Without sample data I'm
also unclear if your required result is to include ALL rows for the latest
date for each fkholding or just ONE row for each.

Is this it?

CREATE TABLE Holding_Values (uid INTEGER NOT NULL, effdate DATETIME NOT
NULL, fkholding INTEGER NOT NULL, assetprice INTEGER NOT NULL, marketvalue
INTEGER NOT NULL /* ? PRIMARY KEY NOT SPECIFIED */)

SELECT H.uid, H.effdate, H.fkholding, H.assetprice, H.marketvalue
FROM Holding_Values AS H,
(SELECT fkholding, MAX(effdate) AS effdate
FROM Holding_Values
WHERE effdate <= @.datevalue
GROUP BY fkholding) AS D
WHERE H.fkholding = D.fkholding
AND H.effdate = D.effdate

--
David Portas
SQL Server MVP
--|||>> have a table named Holding_Value that has several fields [sic] in
it .. <<

Where is the DDL? And tables have columns which are completely
different from fields

>> .. among which are UID, fkHolding, EffDate, Units, MarketValue,
AssetPrice. UID is an identity field [sic] and fkHolding is a foreign
key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table. <<

INDENTITY is never a key and should not be used. There is no magical
"Univerisal Identifier"; do you also believe that God put a 17-letter
hebrew number to everything in Creation? That is how silly using
IDENTITY for a key in an RDBMS is.

A name element tells us what the entity or attribute is in terms of a
data model. You do not use affixes to tell us HOW it is used in one
occurrence in one table. The name "fkHolding" looks slightly obscene
(sorry, but it looks like "F**kHolding" to me).

"Holding_Values" is an attribute, not an entity name. This is a
hisotry, so use that in the name. If you have an asset_price, where is
the asset? I am guessing that used two names for the same entity, so
the holding is the asset.

Get the ISO-11179 Standards or any book on data modeling.

Again, without DDL and proper keys, here is my wild guess:

SELECT H1.*
FROM HoldingHistory AS H1
INNER JOIN
(SELECT asset_id, MAX(eff_date)
FROM HoldingHistory
WHERE eff_date <= @.report_date
GROUP BY asset_id)
AS H2(asset_id, eff_date)
ON H1.asset_id = H2.asset_id
AND H1.eff_date = H2. eff_date;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message >
....
>INDENTITY[sic] is never a key and should not be used. .
....
I keep hearing this, and to some extent agree, however, I also keep seeing
it in use, and when I'm running a
quick scenario, I use identity to generate a key for small data sets.
Can you expound or give links / ref to articles that go into detail on this.

>The name "fkHolding" looks slightly obscene....
fk is used as prefix to indicate Foreign Key.

Kevin Ruggles|||kevin ruggles wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message >
> ...
>>INDENTITY[sic] is never a key and should not be used. .
> ...
> I keep hearing this, and to some extent agree, however, I also keep seeing
> it in use, and when I'm running a
> quick scenario, I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail on this.

Do a google search of this group. It is usually discussed, passionately,
every couple months.

Zach|||Hugo you got it right. I guess i had a brain fart, i was not grouping
by fkHolding when i was trying to do the query. The first approach is a
drag. I let it run for over 2 minutes with no results (indexes might
have something to do wiht it too) but the second one worked like a
charm. It took less than 1 second for a 2.5 million record table. David
Portas solutions works as well.

In response to CELKO's comment about Identity and Primary key I
remember one of my database professors recommending against a while
back ago, but we seem to use that quite often here at work, and i see
identity used a lot as primary key. My prof did not elaborate too much
on why it was a stupid idea to use an identity as primary key but i
would appreciate if someone else has more info.
And fk is a convention often used to mean Foreign Key.

Thanks,

Gent|||> I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail
on this

A search of the web and the microsoft.public.sqlserver.programming
hierarchy will find you many, many articles by Celko and others on this
topic.

> when I'm running a
> quick scenario, I use identity to generate a key for small data sets

What for? IDENTITY is part of a physical implementation not part of the
logical model of your data. If you post a CREATE TABLE statement here,
for example, just with just an IDENTITY column but don't identify any
other key then that tells us nothing about the entities involved and
we'll probably have a much harder time trying to solve your problem. In
modelling and problem-solving scenarios it is usually the natural key
of your data matters. (IDENTITY prompts other problems of its own of
course, but that's where you came in...)

> fk is used as prefix to indicate Foreign Key

Reasonable people differ when it comes to naming conventions. However,
I'll bet if you take a quick poll you'll find that most SQL pros (good
ones anyway) loathe to see prefixes on column and table names. One
reason is that if you represent structure and datatype and other info
in identifiers and then that metadata changes you have to change the
identifier even though the data element itself hasn't changed. Another
reason is that they are harder to type and remember. The standards
document that Joe cited defines some naming conventions for data
elements.

--
David Portas
SQL Server MVP
--|||>> one of my database professors recommending against a while back ago,
but we seem to use that quite often here at work, and I see identity
used a lot as primary key. <<

For the technical reasons that have to do with portability, relational
design and data integrity, you can Google my name and IDENTITY to some
of my rants.

The *real* reasons have to do with human behavior. In the working world,
RDBMS systems come from legacy file systems and untrained legacy file
system programmers. They mimic the designs they learned in the old
technology.

There was little separation of logical and physical data. Physically
contigous fields made up *physically contigous* records in files that
were in a sequence on a magnetic tape. The tape was sorted on a key and
all the EDP depended on that sort order to locate a record -- you did
not do random access on a tape.

Then comes the RDBMS, with the concept of relational keys. This meant
you had to know your data model, you had to do research! That's work!
It is so much easier to use some proprietary exposed physical locator
like IDENTITY or a row number to mimic the physical position of record
at the end of a magnetic tape. You can write code with cursors and
completely mimic a 3GL programming language.

You get to SQL and you have to think in sets and in much more complex
logic. It is hard work if you have never had a class in set theory or
formal logic. So people avoid it with IDENTITY and often miss needed
constraints for real key. It also gives them the feeling that the table
is normalized because it has this "false key" and they leave all kinds
of flaws in the schema. You can clean the results in the front end,
just like you did with COBOL in 1968, right?

>> And fk is a convention often used to mean Foreign Key.<<

I know, I know, but that was too good a straight line not to use :)

Seriously, you name something for *what it is* in the logical data
model, not for *how it is used* in one particular table. Would drop the
FK- prefix if it were used as a non-key column in another table? Do you
use a PK- prefix on it in the the referenced table? Would add "<table
name>-" prefixes for every occurence of the data element? When you sign
a check, do you change your name to include the room you are in at the
time?

The rule is that a data element has one and only one name, one and only
one meaning. This why a data dictionary can work. The only time you
change a data element name is when it occurs in two or more roles in a
query. Thus, "boss_emp_id" and "worker_emp_id" are both "emp_id"
values, but play two different roles.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way 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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> The *real* reasons have to do with human behavior. In the working world,
> RDBMS systems come from legacy file systems and untrained legacy file
> system programmers. They mimic the designs they learned in the old
> technology.
> There was little separation of logical and physical data. Physically
> contigous fields made up *physically contigous* records in files that
> were in a sequence on a magnetic tape. The tape was sorted on a key and
> all the EDP depended on that sort order to locate a record -- you did
> not do random access on a tape.

This is complete bullshit. You and few more people may actually have
programmed against tapes, but most of us haven't.

There's no need to involved tapes and other forms of arcane computer
technology to explain why the concept of an artificial key is popular.
Simpler and even older technique is more applicable, technique that is
still in use: pen and paper.

> It is so much easier to use some proprietary exposed physical locator
> like IDENTITY or a row number to mimic the physical position of record

IDENTITY has nothing to do with a physical location. The row may be
send around - the automatically assigned value for the row will be the
same.

You seriously need to learn how modern RDBMS work, Celko. And unlearn
what you happen to know about magtapes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> This is complete bullshit. You and few more people may actually have
programmed against tapes, but most of us haven't. <<

That does not matter. The newbies are re-discovering tape file systems
without every having worked on them. When you were taking computer
science courses, how many times did you "invent" an algorithm that other
people already knew?

Sequential files are a very natural way to look at data. It mimics
paper forms, index cards and a single processor (person). These evolved
into notched edge cards and then sorted punch cards and then sequential
mag tapes.

>> here's no need to involved tapes and other forms of arcane computer
technology to explain why the concept of an artificial key is popular.
Simpler and even older technique is more applicable, technique that is
still in use: pen and paper. <<

Okay, I'll start telling people that they are 100 years behind instead
of only 50 years behind the technology!

But they do not always re-discover a "pen & paper" model. When they
write nested looping cursors, this is classic COBOL tape merges.

>> IDENTITY has nothing to do with a physical location. <<

So if I put the same row into a new database, I will aways get the same
IDENTITY value? If I put the same row with a real key into a table, I
will aways get the same IDENTITY value?

The IDENTITY column depends on the internal state of a counter in the
PHYSICAL hardware. It is HARDWARE DEPENDENT, just like a ROWID in other
products (but not as fast).

>> You seriously need to learn how modern RDBMS work, Celko. <<

Would you like to see how how many of the really "Modern RDBMS" products
to which I have consulted on implementation issues? While I was on the
X3H2 Committee, a lot of my consulting work was reading and explaining
how SQL was supposed to work to new RDBMS developers.

The Nucleus engine, which uses compressed bit vectors for the entire
schema was a client. WATCOM SQL (now part of Sybase) and their single
index structure that ties PK-FK together in one structure was a client
(you still have to index the FK side in SQL Server -- the tables are
seen as disjoint, not related inside a total schema). Looked at minor
stuff at Teradata, Etc. I have been all over the insides of the Modern
RDBMS.

Right now, SQL Server is still based on a file system that uses
contigous storage and is 20+ years old in its basic architecture. So is
DB2 and Oracle really stinks. If RDBMS developers did not understand
thinking in sets or viewing the schema as an integrated whole, instead
of disjoint tables (files), why would application developers understand
non-procedural algorithms?

>> And unlearn what you happen to know about magtapes. <<

No, I need to know when a magtape model of data processing is
appropriate. It works very nicely in ETL jobs for a data warehouse with
parallelism and scrubbing. I also need to remember it well enough to
know it when I see it and avoid it in an OLTP environment.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way 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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> That does not matter. The newbies are re-discovering tape file systems
> without every having worked on them. When you were taking computer
> science courses, how many times did you "invent" an algorithm that other
> people already knew?

Then probably that tells us something that this is a good model that is
easy to work with. If you want to convince people that it is not, you
should not talk about tapes, because they will not understand what you
are talking about.

An autonumber is a convenient way of numbering rows in the order they
are entered in a database. Sometimes, this is not very useful, sometimes
this is the only way to identify data. (Example: you load a file from
an external source. The file may be supposed to come with real keys, but
you cannot be sure that the file adheres to its supposed format. So the
only key when it comes to describe the file is the line number. (Then
after examining the data, you might be able to get the data into the
target table with some other keys.)

> So if I put the same row into a new database, I will aways get the same
> IDENTITY value? If I put the same row with a real key into a table, I
> will aways get the same IDENTITY value?

Yes, once assigned the value will not change. Do you really think that
if take copy of a database, that all identity values gets replaced? Would
be quite a useless feature.

> The IDENTITY column depends on the internal state of a counter in the
> PHYSICAL hardware. It is HARDWARE DEPENDENT, just like a ROWID in other
> products (but not as fast).

So, if the database is moved from one machine to another, and the last
inserted row before the move got an IDENTITY value of 987, the next
inserted after the move could get the value -234, 876 or 23445?

Nonsense. The IDENTITY value has nothing to do with the hardware, but
it does reflect in which order the rows where inserted. (Although, there
is now guarantee that this is he case.)

>>> You seriously need to learn how modern RDBMS work, Celko. <<
> Would you like to see how how many of the really "Modern RDBMS" products
> to which I have consulted on implementation issues?

I don't have to see the list. I just see how many inaccurate statements
you make about SQL Server, and that tells me much more that any lists
you may produce.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Easy query question

Hello all,

I have a table representing a tree structure with three fields, let's call them this for now:

ID, Name, ParentID

ID is an autonumber. ParentID points back to an ID.

I need to find all those records that are not parents of other records. That is, I need to SELECT * FROM myTable WHERE (my ID is not in the ParentID column for any record.)

I just need to translate that last bit ("my ID is not in the ParentID column for any record") into SQL.

Any help would be greatly appreciated!!

Thanks,
FarazSELECT * from tbl t1 left outer join tbl t2 on t1.ID = t2.ParentID where t2.ParentID is null|||Not to be to fancy using having clause,

SELECT * FROM myTable WHERE ID not in (select ParentID from mytable)|||SELECT c.*
FROM myTable c
LEFT JOIN myTable p
ONc.ID = p.ParentId
WHERE p.ParentId IS NULL

OR

SELECT c.*
FROM myTable c
WHERE NOT EXISTS
( SELECT * FROM myTable p
WHERE c.ID = p.ParentId)

OR

SELECT *
FROM myTable
WHERE NOT IN
( SELECT ParentId FROM myTable)


Your Choice|||Thanks! I knew this was easy, and I know I've done it before, but somehow kept getting either too many or too few results.

Out of all your generous replies, I selected:

"SELECT * FROM myTable WHERE ID NOT IN (select ParentID from MyTable)"|||Your choice to do it right or wrong.

Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

blindman|||Originally posted by blindman
Your choice to do it right or wrong.

Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

blindman

it depends.....

Change the IN one to this

SELECT *
FROM myTable
WHERE ID NOT IN
( SELECT DISTINCT ParentId FROM myTable)

If there is a low cardinality, this might be most effecient...

You really need to a show plan to see which is most effecient (Blindman's right though, the Join usually wins)|||Okay, if you insist...

I'm now using the Joins... everything works swell.

Thanks everyone.|||Originally posted by Brett Kaiser
You really need to a show plan to see which is most effecient

Well actually I'd rather "insist" you do a SHOWPLAN on All three to see which one performs the best for you.

This is normal SQL development...|||If you use "not in ("... Then make sure to either "set ansi_nulls off" or add "where parentid is not null" - otherwise, no records will be returned if there is a parentid which is null. For example:

SELECT *
FROM myTable
WHERE ID NOT IN
( SELECT DISTINCT ParentId FROM myTable where parentid is not null)

easily editting fields with more than 1024 characters

i suspect i know the answer to this already, but here goes anyway...

i have a table that has field of varchar(2048), which once in a blue moon i
need to edit the data manually (until a bad character parser validates the
data before it's written ;-) )
at the moment i'm doing this through enterprise manager (sql server 2000),
opening the table then filtering using where clauses etc to see the records
i'm interested in. i then edit the data direct in the results pane (purely
because it's quicker than entering the UPDATE transact SQL). this is fine
until i hit a record that has 1024 or more characters in the field. all i
can do is delete all the data. if i try and paste the same data into the
field again, it'll truncate the record to the first 1024 chars (unconfirmed)
despite the field being able to take double that.

i've googled this and the result basically said "don't be lazy, do it
through UPDATE transact SQL in the query analyser".

anyone know if that's my only option or is there a patch / whatever to allow
me to keep using entman as i lazily do at the mo?

cheers!dave (usenet@.polo.devilgas.com) writes:
> i suspect i know the answer to this already, but here goes anyway...
> i have a table that has field of varchar(2048), which once in a blue
> moon i need to edit the data manually (until a bad character parser
> validates the data before it's written ;-) ) at the moment i'm doing
> this through enterprise manager (sql server 2000), opening the table
> then filtering using where clauses etc to see the records i'm interested
> in. i then edit the data direct in the results pane (purely because it's
> quicker than entering the UPDATE transact SQL). this is fine until i hit
> a record that has 1024 or more characters in the field. all i can do is
> delete all the data. if i try and paste the same data into the field
> again, it'll truncate the record to the first 1024 chars (unconfirmed)
> despite the field being able to take double that.
> i've googled this and the result basically said "don't be lazy, do it
> through UPDATE transact SQL in the query analyser".
> anyone know if that's my only option or is there a patch / whatever to
> allow me to keep using entman as i lazily do at the mo?

As I just said in another post, the Open Table function in Enterprise
Manager is a convenience function and not a replacement for an application
or even in class with Access or Excel. There are several shortcomings
with Open Table. In this particular case, I believe there is a limit of
around 1000 characters io EM.

So you just start typing UPDATE commands. By the time, you've gotten some
exercise, you will find that that is faster in the long run, because
scripts are repeatable, while point-and-click GUIs are not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9698EF7C2DE54Yazorman@.127.0.0.1...
> As I just said in another post, the Open Table function in Enterprise
> Manager is a convenience function and not a replacement for an application
> or even in class with Access or Excel. There are several shortcomings
> with Open Table. In this particular case, I believe there is a limit of
> around 1000 characters io EM.
> So you just start typing UPDATE commands. By the time, you've gotten some
> exercise, you will find that that is faster in the long run, because
> scripts are repeatable, while point-and-click GUIs are not.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

thanks erland. i can assure you that in this case, it's a lot faster doing
it from EM than transact SQL. by the time i've copied and pasted the 1000+
characters, id field etc etc then edit the chars i want to change in the SQL
it's considerably longer.
thankfully these changes are once in a blue moon and only affect a single
record.

Easiest way of combining multiple fields from different records into one record?

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')

I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

...but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!Just add MIN():

SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals

--
David Portas
SQL Server MVP
--|||Hi

Maybe:
SELECT A.defaultServer, B.defaultShare, C.defaultFolder, D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D

OR

SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile

You should put a unique or primary key on theKey to make sure only one row
is returned.

John

"Steve" <steve.lin@.cognizantdesign.com> wrote in message
news:27b20cea.0407090955.690c2c8b@.posting.google.c om...
> I have a table;
> CREATE TABLE theLiterals (
> theKey varchar (255) NOT NULL ,
> theValue varchar (255) NULL
> )
> INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
> INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
> INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
> INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
>
> I then try;
> SELECT
> defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
> defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
> defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
> defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
> FROM theLiterals
> and I get;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ NULL NULL NULL
> NULL MyShare\ NULL NULL
> NULL NULL MyFolder\ NULL
> NULL NULL NULL MyFile.dat
> but I want it COALESCEd like this;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> ...but my syntax is incorrect. Is there an efficient way of doing this.
> I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
> and then my one-row recordset will be...
> RS(0) will = '\\MyServer\'
> RS(1) will = 'MyShare\'
> RS(2) will = 'MyFolder\'
> RS(3) will = 'MyFile.dat'
> Thanks for any help!|||How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer'),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare'),
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder'),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\MyShare\MyFolder\MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman|||Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:

SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals

"Jim Geissman" <jim_geissman@.countrywide.com> wrote in message
news:b84bf9dc.0407091511.6338405b@.posting.google.c om...
> How about this:
> SELECT TOP 1
> defaultServer = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultServer'),
> defaultShare = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultShare'),
> defaultFolder = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFolder'),
> defaultFile = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFile')
> FROM #theLiterals
> That returns the desired record:
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> Or you could create a function that takes 4 parameters like
'defaultServer'
> and returns a one-record table populated with the results from those 4
> SELECTs.
> Jim Geissman|||And first place for minimum reads goes to David Portas!

Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.

FYI, I listed each of your solutions and the number of reads each
took and some additional questions.

NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.

-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals

-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals

-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile

-- John Bell
-- 24 reads
SELECT A.defaultServer, B.defaultShare, C.defaultFolder,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D

-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer'),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare'),
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder'),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals

Each Record on New Page

I have designed a report which is picking up the records from a sharepoint list with around 40-50 fields in it. As I was not able to fit all in a list view, therefore I designed the report in a profile view. I did this by using tables and putting the fields in the 'header' of the table rather than the 'Detail' section.

The report is looking quite good. As there can be more then one records I use the 'Last' function in order to ensure that the latest records are displayed.

What shall I do if I want to display all the records. Meaning each record can come on a new page. Is there something I can do, to do this.

Thanks

Amad

Set the PageBreakAtEnd property to true on each table you have.

|||

I need to display different blocks of information. e.g. Student Basic Information, Student Contact Information, Student Course Information etc.

For each block I have a made a separate table. I have rows of 'Header' type since if I take the 'Details' type of rows then rows will be displayed on one page. All the blocks of information are displayed on one page. Therefore each page contains information for one student

By your solution its giving a page break between two tables (or groups of information). What I want, is to display each student int a new page.

Thanks

|||

IIRC you can set grouping information on a list control, so you can place your tables in a list control, set the grouping to student name or ID, and have a page break at the end of each list.

sql

dynanic report with checkbox control

hi all ,
(Using C#.net) there are 4 fields (name , family name , ave , stNumber )in my table . for this , in my form(form1) I've 4 CheckBox Controls . I want to take a report whenever the user select one or two or ... of them . for example , if the user checked CheckBox1(for the name field ) and CheckBox2(for the family field) , In outpout report show , name and family . how can i dot with crystal report ?
plz help me .In Form1 I've a checkbox control . I created a parameter in crystal report .
this is my code :
in btnReport_Click :

{
string strQuery = "SELECT Name FROM MyTable";
sqlconn.Open();
daAdapter = new SqlDataAdapter(strQuery, sqlconn);
SqlCommandBuilder scb = new SqlCommandBuilder(daAdapter);
da.Fill(DatatSet1.MyTable);
//Definitions
ParameterField paramfield = new ParameterField();
ParameterFields paramfields = new ParameterFields();
ParameterDiscreteValue discreteval = new ParameterDiscreteValue();
//setting
paramfield.Name = "Name";
discreteval.Value = ??
paramfield.CurrentValues.Add(discreteval);
paramfields.Add(paramfield);
crystalReportViewer1.ParameterFieldInfo = paramfields;crystalReportViewer1.ReportSource = crystalreport1;
sqlconn.Close();}

I want to take a report from all of records' Name field of the Table When The user checked "CheckBox1" Control .
plz help me in this :
discreteval.Value = ??
thanx|||You can use global variables

Public gblnShowName As Boolean

In Command button Sub, set the value

If chkShowName.Value = vbChecked Then
gblnShowName = True
Else
gblnShowName = False
End If

Then inside the report code section,

If gblnShowName = True Then
Report.NameField.Suppress = False
Else
Report.NameField.Suppress = True
End If

I normally code like this in VB6, hope it works in .NET also.|||hi,
It doesn't work .|||at last it was solved .
in report sheet , right click on any field , and select Format Object and then checked on suppress CheckBox . after that , in Form1.cs , in btnShowReport_Click , write this :
myCrystalReport1.ReportDefinition.Sections[2].ReportObjects["firstname1"].ObjectFormat.EnableSuppress = false;

Dynamically collapsing textboxes (and adjusting layout)

Just curious if there's a way I can remove a field from my report at run time and shift all the fields underneath it up.

I basically want to end up with the following:

Design time=============================Field 1: Fields!Field1.ValueField 2: Fields!Field2.Value// This one will be blankField 3: Fields!Field3.ValueField 4: Fields!Field4.ValueRun Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"

Problem is, if I set the visibility of the field to false, it still takes up space on the form (as it should). Any suggestions as to how to shift all the fields up without using a table?

Thanks!

I guess I should clairify a bit

This is what is happening now:

Run Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"I want to collapse Field 2 up so that thereis no whitespace between 1 and 3

Not sure if this is even possible...

Dynamically CHange Table Name

Hi All...
How to dynamically change the Table name which i have use at design time. if old and new table contains same fields but changing only table name.(Old- at Design Time
New - At Run TIme)

I would prefer not to have to go through the hassle of manually opening up my Crystal Report documents in Visual Studio and setting their new table name each and every time I want to make a change to the table name dynamically.

Hope So....
Thanks in Advance.

Regards
Henry Jones.I think this is difficult to do this
See if you find solution here
http://support.businessobjects.com/

Dynamically adding fields to a report.

Not sure how to tackle this one. I need to create a report that dynamically
adds fields based on information from a data store.
[Abstracting the problem for clarity]
Let's say I have a table with contact data (FirstName, LastName, Street,
City, State, Zip) and a customer table with a definition of what contact
fields they use and how to render them.
I need a report that I pass a customerId and it looks up what fields to
pull, say customer1's report would look like:
FirstName LastName
Zip
And customer2's report would look like
FullName (Combining First and LastName)
Street
City, Zip
Each report needs to only show the relavant data, and additionlly position
it according the cutomer's defined format. Creating a report for each
customer is out of the question, way too many customers, and that's just
retarded. I thought about adding every field to the report and hiding the
ones that weren't used but positioning would be a headache.
Is there a way that I can dynamically create and add fields to the report at
runtime?
Any ideas would be greatly appriciated.Sorry, dupe of a previous submit. See Below.|||The short answer is no, since dynamically adding report elements is not
supported in version 1.0 of Reporting Services. That said, it looks like the
best workaround in your case is to pre-process the report by loading the
report definition in XML DOM and add/remove the fields you don't need. This
will require an application front-end to generate the report definition,
upload and generate the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> Not sure how to tackle this one. I need to create a report that
dynamically
> adds fields based on information from a data store.
> [Abstracting the problem for clarity]
> Let's say I have a table with contact data (FirstName, LastName, Street,
> City, State, Zip) and a customer table with a definition of what contact
> fields they use and how to render them.
> I need a report that I pass a customerId and it looks up what fields to
> pull, say customer1's report would look like:
> FirstName LastName
> Zip
> And customer2's report would look like
> FullName (Combining First and LastName)
> Street
> City, Zip
> Each report needs to only show the relavant data, and additionlly position
> it according the cutomer's defined format. Creating a report for each
> customer is out of the question, way too many customers, and that's just
> retarded. I thought about adding every field to the report and hiding the
> ones that weren't used but positioning would be a headache.
> Is there a way that I can dynamically create and add fields to the report
at
> runtime?
> Any ideas would be greatly appriciated.|||You need something like ad-hoc right?
It will be available with SQL 2005.
But right now you can dynamicly build query in SQL. I did it once. it
is not perfect report but idea is :
1. create parameter string where user will put field they want to see
using coma delimeter
2. pass this string on SP and parse it into the table.
generate query what return you what ever you need just remember each
field should be name generic (col1, col2, col3) and sequence should be
how they want to see it on the screen
3. create report base on col1, col2 col3 data returned.
If you need more info please send me email(natta@.netzero.net). I will
try to send you example.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message news:<#KjqSlqzEHA.1564@.TK2MSFTNGP09.phx.gbl>...
> The short answer is no, since dynamically adding report elements is not
> supported in version 1.0 of Reporting Services. That said, it looks like the
> best workaround in your case is to pre-process the report by loading the
> report definition in XML DOM and add/remove the fields you don't need. This
> will require an application front-end to generate the report definition,
> upload and generate the report.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
> news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> > Not sure how to tackle this one. I need to create a report that
> dynamically
> > adds fields based on information from a data store.
> >
> > [Abstracting the problem for clarity]
> > Let's say I have a table with contact data (FirstName, LastName, Street,
> > City, State, Zip) and a customer table with a definition of what contact
> > fields they use and how to render them.
> >
> > I need a report that I pass a customerId and it looks up what fields to
> > pull, say customer1's report would look like:
> > FirstName LastName
> > Zip
> >
> > And customer2's report would look like
> > FullName (Combining First and LastName)
> > Street
> > City, Zip
> >
> > Each report needs to only show the relavant data, and additionlly position
> > it according the cutomer's defined format. Creating a report for each
> > customer is out of the question, way too many customers, and that's just
> > retarded. I thought about adding every field to the report and hiding the
> > ones that weren't used but positioning would be a headache.
> >
> > Is there a way that I can dynamically create and add fields to the report
> at
> > runtime?
> >
> > Any ideas would be greatly appriciated.

Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub

Dynamic WHERE operator based on user input

Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:

DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".

Age: This text box is where someone would enter a number.

Button1: This is the form's submit button.

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:

"The variable name'@.Age' has already been declared. Variable names must be unique within a query batch or stored procedure."

Any help would be appreciated.

Here is what I'm using in my code behind:

protected void Button1_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM People WHERE Age ");
switch (DropDownList1.SelectedValue)
{
case "=":
sb.Append("= ");
break;
case ">":
sb.Append("> ");
break;
case "<":
sb.Append("< ");
break;
}
sb.Append("@.Age");
SqlDataSource1.SelectCommand = sb.ToString();
SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text);
}

i would create 3 stored procedures first with "<" second with "=" and third with ">"

then, in code behind

if (DropDownList1.SelectedValue.ToString() == "<") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("LessThan_toate", conn);//cnn = your conn string command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader;// your dataReader DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() =="=") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("EqualsWith", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() ==">") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("GreatherThen", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }

sure, my stored procedures doesn't have input parameters but you can change easly that

|||

adammckee:

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

So basically you want everything in the table? what are you excluding?

|||

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||Either I don't understand what you're doing or you're making it more complicated than it should be. Wouldn't this work?

Private Sub cmd_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmd.ClickDim sQueryAs String sQuery ="SELECT * FROM People WHERE Age " &Me.DropDownList1.SelectedValue &" " &Me.txtAge.Text SqlDataSource1.SelectCommand = sQueryEnd Sub

|||

This worked nicely, but doesn't this leave me open to SQL injection? I was attempting to do this with the SelectParameters method to avoid that.

To clarify what I'm trying to do for the other person that asked, I want to give the user the ability to search for people in the database based on their age. If the user wants to search for anyone that's over 50, they pull down the drop down list, select the ">" operator then type "50" in the text field and perform the search.

ecbruck:

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||

If you're worried about it, then go ahead and use a Parameter instead. However, I would at least recommend declaratively placing the Parameter within your SqlDataSource on your ASPX page and then simply setting the DeefaultValue of the property within your Click event handler or the SqlDataSource.Selecting event handler.

|||

For anyone interested, this is what works for me. Thanks again for the advice.

1protected void Button1_Click(object sender, EventArgs e)2 {3 System.Text.StringBuilder sb =new System.Text.StringBuilder();4 sb.Append("SELECT * FROM People WHERE Age ");5switch (DropDownList1.SelectedValue)6 {7case"=":8 sb.Append("= ");9break;10case">":11 sb.Append("> ");12break;13case"<":14 sb.Append("< ");15break;16 }17 sb.Append("@.Age");18 SqlDataSource1.SelectCommand = sb.ToString();19if (SqlDataSource1.SelectParameters["Age"] ==null)20 SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, TextBox1.Text);21else22 SqlDataSource1.SelectParameters["Age"].DefaultValue = TextBox1.Text;23 }

Dynamic WHERE Clause to Stored Procedure

Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

----------------
@.crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @.crit
----------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelleU just pass a parameter @.crit into ur stored procedure and do the following with it inside:

EXEC('SELECT fldID, fldName FROM tblUsers WHERE' + @.crit );

This should help u... I hope.
Alex.|||Another example:

CREATE PROCEDURE [dbo].[Alter_Email_Users_Table]
(@.Column as nvarchar(50),
@.FieldType as nvarchar(50),
@.TableName as nvarchar(50),
@.Null as nvarchar(20),
@.Default as nvarchar(4000)
)
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'ALTER TABLE ' + @.TableName + ' ADD ' + @.Column + ' ' + @.FieldType + ' ' + @.NULL + ' '
IF @.Default IS NOT NULL
SET @.SQL = @.SQL + ' Default ' + "'" + @.Default + "'"
exec (@.SQL)
GO

exec is the key part in both examples.

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic table name and fields

I need to get the field values of a table (name will be dynamic).
Then assign those values to properties in a class.

Let's say I will get the table name dynamically.

dim tblName as string = "tablea"

The 2 tables can each have 25 fields or so.

I need a way to select the amt and email field values from tblName.
Without saying "select job_amt, job_email from ...

Is there someway to get the values based on the column name.
So if the column name has amt and email, then give me those values.

Maybe loop through the datatable - then for each column --
if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 then
then drop that column from the datatable.

ex of table structure

<u>tablea</u>
job_id
job_amt
job_email

<u>tableb</u>
dance_id
amt_dance
dance_email

I recommend you to use reflection to solve this task. You can create a simple object relational mapper to map automatically the table columns on class properties. Basic idea you can see at following code snippet (your concrete class will inherit from BusinessObject class):

public abstract class BusinessObject{...protected bool Select(string storedProcedureName,params SqlParameter[] parameters){bool status =false;Type objType = GetType();using (SqlConnection conn =new SqlConnection(ConnectionString.GetConnectionString())){using (SqlCommand cmd =new SqlCommand(storedProcedureName, conn)){cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddRange(parameters);cmd.Parameters.Add("@.ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;conn.Open();using (SqlDataReader reader = cmd.ExecuteReader()){if (reader.Read()){int count = reader.FieldCount;for (int i = 0; i < count; i++){if (reader.IsDBNull(i))continue;PropertyInfo property = objType.GetProperty(reader.GetName(i));if (property !=null) property.SetValue(this, reader.GetValue(i),null);}status =true;}}returnValue = Convert.ToInt32(cmd.Parameters["@.ReturnValue"].Value);conn.Close();}}return status;}...}

|||Don't I need to create a class for this first. What if I want to do this without creating a class.|||OK, if you can make sure there is only one table which contains?all?given?columns,?try?such?stored?procedure:

CREATE PROC sp_GetDataForCols
AS
BEGIN
DECLARE @.tblName sysname

SELECT @.tblName=OBJECT_NAME(c.id) FROM syscolumns c join sysobjects o
on o.id=c.id
WHERE c.name='OrderID'
AND o.type='U'
AND EXISTS(SELECT 1 FROM syscolumns i
WHERE c.id=i.id AND i.name='CustomerID')
EXEC('SELECT OrderID,CustomerID FROM '+@.tblName)
END

You can modify the stored procedure to add a varchar parameter for inputting column names delimited by some char(s), which can be broken down into individual column name. Then use the column names to build dynamic statement to find the table name. Well, seems not so easy? Yeah it's always not so easy to do some "strange" thing with T-SQL. BTW, the performance of executing dynamic T-SQL is not so good.?

Dynamic Table Creation

Hi
I wish to create a table through sql code which picks up fields from
different tables in the database.
Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
Is this possible? Any help is greatly appreciated. Thanks!
MJTry,
select a.w, a.x, b.y, b.z
into tablec
from tablaA as a inner join tableb as b on 0 = 1
This will not create constraints. You have to alter tablc and add them
manually.
AMB
"MJ" wrote:

> Hi
> I wish to create a table through sql code which picks up fields from
> different tables in the database.
> Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
> and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
> Is this possible? Any help is greatly appreciated. Thanks!
> MJ|||MJ,
In order to give a query for this, you need to provide the rule
that explains when to put TableA.W and TableA.X in the same
output row as TableB.Y and TableB.Z.
For example, say TableA is PeoplePhoneNumbers, and has columns
(Name,Phone), and TableB is CarLicenses with tables (VINnumber, License),
how do you match up name,phone pairs with VINnumber, license pairs?
Even if you don't care how things are matched up, you still need to decide
on a specific rule to follow.
Steve Kass
Drew University
MJ wrote:

>Hi
>I wish to create a table through sql code which picks up fields from
>different tables in the database.
>Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
>and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
>Is this possible? Any help is greatly appreciated. Thanks!
>MJ
>|||Hi yes using AMB's code it worked fine. I dont really care how they match
up and I think the constaint 0 = 1 allows for that.
Thanks both you guys!
MJ
"Steve Kass" wrote:

> MJ,
> In order to give a query for this, you need to provide the rule
> that explains when to put TableA.W and TableA.X in the same
> output row as TableB.Y and TableB.Z.
> For example, say TableA is PeoplePhoneNumbers, and has columns
> (Name,Phone), and TableB is CarLicenses with tables (VINnumber, License),
> how do you match up name,phone pairs with VINnumber, license pairs?
> Even if you don't care how things are matched up, you still need to deci
de
> on a specific rule to follow.
> Steve Kass
> Drew University
> MJ wrote:
>
>|||Columns are not fields; rows are not records. This is basic.
It sounds like you want to create a table on the fly, after the data
model is implemented. Surely not! That would mean that you have no
data model yet and should not have implemented a schema. You can
kludge it, but yu can also learn to be a good SQL programmer instead.|||Ah - I was under the assumption you wanted to put data into the
table, not just create an empty table. Alejandro's suggestion is
an excellent one for what you want.
SK
MJ wrote:
>Hi yes using AMB's code it worked fine. I dont really care how they match
>up and I think the constaint 0 = 1 allows for that.
>Thanks both you guys!
>MJ
>"Steve Kass" wrote:
>
>

Dynamic Stored Procedures uses vars only

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

Writing dynamic T-SQL doesn't strike me as being relevant to SSIS so I'm a little confused. Perhaps you could elaborate.

By the way, best practice stipulates that you shouldn't name your sprocs "sp_*".

-Jamie

dynamic SQL versus a huge IF structure

Hi,
I have a stored proc intented to search a table on different fields,
depending on the search type.
Up until now, I have implemented 3 options, which is by kanji, kana and
english. I'll add many others, like strokes count, radical # and so on.
Here it is, with the 3 options (it may seem overwhelming but is only
because of the IF ELSE structure) :
... PROCEDURE [dbo].[DicKanjiSearch] @.search nvarchar(200), @.type
tinyint,
@.search2 nvarchar(200) = '', @.detailed bit AS
-- @.type : 1 = kanji
-- 2 = english
-- 3 = hiragana or katakana : pronunciation (on'yomi / kun'yomi
/ nanori)
-- @.search2 is there in case we have to search by pronunciation, we
must
-- be able to search in hiragana and katakana. In that case,
-- on'yomi (chinese) pronunciation is in KATAKANA ans is @.search.
-- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.search2.
-- nanori (name) pronunciation is also in HIRAGANA ans therefore is
@.search2.
BEGIN
SET NOCOUNT ON
-- return fields for detailed listing
IF @.detailed = 1
BEGIN
IF @.type = 1
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #KanjiIdsByKanji
FROM dbo.Kanjis
WHERE kanji_kanji = @.search
-- select kanji fields
SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
lk_filename, lk_idlesson,
lesson_idlevel, lesson_idlesson,
kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
FROM #KanjiIdsByKanji INNER JOIN Kanjis ON kanji_id = id
LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
.. [ * comment: I removed some other table select for the sake of
the simplicity post ]
END
ELSE IF @.type = 2
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #KanjiIdsByEn
FROM dbo.Kanjis
WHERE FREETEXT (kanji_meaning, @.search)
-- select kanji fields
SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
lk_filename, lk_idlesson,
lesson_idlevel, lesson_idlesson,
kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
FROM #KanjiIdsByEn INNER JOIN Kanjis ON kanji_id = id
LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
.. [ * comment: I removed some other table select for the sake of
the simplicity post ]
END
ELSE IF @.type = 3
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #KanjiIdsByKana
FROM dbo.Kanjis
WHERE FREETEXT (kanji_on, @.search)
UNION ALL
SELECT kanji_id as id
FROM dbo.Kanjis
WHERE FREETEXT (kanji_kun, @.search2)
UNION ALL
SELECT kanji_id as id
FROM dbo.Kanjis
WHERE FREETEXT (kanji_nanori, @.search2)
-- select kanji fields
SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
lk_filename, lk_idlesson,
lesson_idlevel, lesson_idlesson,
kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
FROM #KanjiIdsByKana INNER JOIN Kanjis ON kanji_id = id
LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
.. [ * comment: I removed some other table select for the sake of
the simplicity post ]
END
END
ELSE
-- return fields for basic listing
BEGIN
IF @.type = 1
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #BasicKanjiIdsByKanji
FROM dbo.Kanjis
WHERE kanji_kanji = @.search
SELECT kanji_kanji, kanji_meaning
FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id = id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
END
ELSE IF @.type = 2
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #BasicKanjiIdsByEn
FROM dbo.Kanjis
WHERE FREETEXT (kanji_meaning, @.search)
SELECT kanji_kanji, kanji_meaning
FROM Kanjis INNER JOIN #BasicKanjiIdsByEn ON kanji_id = id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
END
ELSE IF @.type = 3
BEGIN
-- select kanji ids corresponding to the search string
SELECT kanji_id as id
INTO #BasicKanjiIdsByKana
FROM dbo.Kanjis
WHERE FREETEXT (kanji_on, @.search)
UNION ALL
SELECT kanji_id as id
FROM dbo.Kanjis
WHERE FREETEXT (kanji_kun, @.search2)
UNION ALL
SELECT kanji_id as id
FROM dbo.Kanjis
WHERE FREETEXT (kanji_nanori, @.search2)
SELECT kanji_kanji, kanji_meaning, kanji_isjouyou
FROM Kanjis INNER JOIN #BasicKanjiIdsByKana ON kanji_id = id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
ASC
END
END
END
Okay, up until now, it's not too bad, I have a first IF to check if I
must return a lot of columns (detailed) or only a few (basic). Then, in
each, I have another IF for each search type, to search the correct
field.
I'd like to know on a performance point of view what is best? Continue
that way and add an IF for each new type (of course, in both detailed
and basic), or use dynamic SQL?
Also, if the answer is the IF ELSE structure, what would be a good way
to implement multiple search types (for example, by english meaning AND
strokes number). Because for now, I am limited to one search type per
query.
Thanks for all your comments and suggestionsHi
Check out http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dyn-search.html.
It may be possible to rationalise the use of the different temporary tables
into one, in which case your second select might possibly be made the same o
r
more similar and not produce different result sets for each combination,
which may clean up the client code.
You may also want to look at splitting the different sections into their own
stored procedures.
John
"ibiza" wrote:

> Hi,
> I have a stored proc intented to search a table on different fields,
> depending on the search type.
> Up until now, I have implemented 3 options, which is by kanji, kana and
> english. I'll add many others, like strokes count, radical # and so on.
> Here it is, with the 3 options (it may seem overwhelming but is only
> because of the IF ELSE structure) :
> ... PROCEDURE [dbo].[DicKanjiSearch] @.search nvarchar(200), @.type
> tinyint,
> @.search2 nvarchar(200) = '', @.detailed bit AS
> -- @.type : 1 = kanji
> -- 2 = english
> -- 3 = hiragana or katakana : pronunciation (on'yomi / kun'yomi
> / nanori)
> -- @.search2 is there in case we have to search by pronunciation, we
> must
> -- be able to search in hiragana and katakana. In that case,
> -- on'yomi (chinese) pronunciation is in KATAKANA ans is @.search.
> -- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.search2.
> -- nanori (name) pronunciation is also in HIRAGANA ans therefore is
> @.search2.
> BEGIN
> SET NOCOUNT ON
> -- return fields for detailed listing
> IF @.detailed = 1
> BEGIN
> IF @.type = 1
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #KanjiIdsByKanji
> FROM dbo.Kanjis
> WHERE kanji_kanji = @.search
> -- select kanji fields
> SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
> kanji_nanori, kanji_meaning,
> lk_filename, lk_idlesson,
> lesson_idlevel, lesson_idlesson,
> kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
> FROM #KanjiIdsByKanji INNER JOIN Kanjis ON kanji_id = id
> LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
> LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> ... [ * comment: I removed some other table select for the sake of
> the simplicity post ]
> END
> ELSE IF @.type = 2
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #KanjiIdsByEn
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_meaning, @.search)
> -- select kanji fields
> SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
> kanji_nanori, kanji_meaning,
> lk_filename, lk_idlesson,
> lesson_idlevel, lesson_idlesson,
> kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
> FROM #KanjiIdsByEn INNER JOIN Kanjis ON kanji_id = id
> LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
> LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> ... [ * comment: I removed some other table select for the sake of
> the simplicity post ]
> END
> ELSE IF @.type = 3
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #KanjiIdsByKana
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_on, @.search)
> UNION ALL
> SELECT kanji_id as id
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_kun, @.search2)
> UNION ALL
> SELECT kanji_id as id
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_nanori, @.search2)
> -- select kanji fields
> SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
> kanji_nanori, kanji_meaning,
> lk_filename, lk_idlesson,
> lesson_idlevel, lesson_idlesson,
> kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
> FROM #KanjiIdsByKana INNER JOIN Kanjis ON kanji_id = id
> LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
> LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> ... [ * comment: I removed some other table select for the sake of
> the simplicity post ]
> END
> END
> ELSE
> -- return fields for basic listing
> BEGIN
> IF @.type = 1
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #BasicKanjiIdsByKanji
> FROM dbo.Kanjis
> WHERE kanji_kanji = @.search
> SELECT kanji_kanji, kanji_meaning
> FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id = id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> END
> ELSE IF @.type = 2
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #BasicKanjiIdsByEn
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_meaning, @.search)
> SELECT kanji_kanji, kanji_meaning
> FROM Kanjis INNER JOIN #BasicKanjiIdsByEn ON kanji_id = id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> END
> ELSE IF @.type = 3
> BEGIN
> -- select kanji ids corresponding to the search string
> SELECT kanji_id as id
> INTO #BasicKanjiIdsByKana
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_on, @.search)
> UNION ALL
> SELECT kanji_id as id
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_kun, @.search2)
> UNION ALL
> SELECT kanji_id as id
> FROM dbo.Kanjis
> WHERE FREETEXT (kanji_nanori, @.search2)
> SELECT kanji_kanji, kanji_meaning, kanji_isjouyou
> FROM Kanjis INNER JOIN #BasicKanjiIdsByKana ON kanji_id = id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201)
> ASC
> END
> END
> END
>
> Okay, up until now, it's not too bad, I have a first IF to check if I
> must return a lot of columns (detailed) or only a few (basic). Then, in
> each, I have another IF for each search type, to search the correct
> field.
> I'd like to know on a performance point of view what is best? Continue
> that way and add an IF for each new type (of course, in both detailed
> and basic), or use dynamic SQL?
> Also, if the answer is the IF ELSE structure, what would be a good way
> to implement multiple search types (for example, by english meaning AND
> strokes number). Because for now, I am limited to one search type per
> query.
> Thanks for all your comments and suggestions
>|||Hi, thanks for your reply.
By the way, you gave me the two same links, is that a typo?

> It may be possible to rationalise the use of the different temporary table
s
> into one, in which case your second select might possibly be made the same
or
> more similar and not produce different result sets for each combination,
> which may clean up the client code.
How would you do that? I have trouble to get it :S
thanks for your time
John Bell wrote:[vbcol=seagreen]
> Hi
> Check out http://www.sommarskog.se/dyn-search.html and
> http://www.sommarskog.se/dyn-search.html.
> It may be possible to rationalise the use of the different temporary table
s
> into one, in which case your second select might possibly be made the same
or
> more similar and not produce different result sets for each combination,
> which may clean up the client code.
> You may also want to look at splitting the different sections into their o
wn
> stored procedures.
> John
> "ibiza" wrote:
>|||Hi
"ibiza" wrote:

> Hi, thanks for your reply.
> By the way, you gave me the two same links, is that a typo?
Yes it was a cut and paste issue! Try
http://www.sommarskog.se/dynamic_sql.html

>
> How would you do that? I have trouble to get it :S
Looking at the code you seem to have very similar temporary tables with
different name. Without looking in depth it struck me that if you just
created a single table at the begining you may be able to see more
rationalisation. I would have expected the current client to have similar
code to the stored procedure as you seem to be producing unique output for
each one. This could probably be cleaned up if you had the same column names
for everything. If you took this further and always returned the same column
s
then you may be able to clean up the client coding further, the downside may
be that you produce a wider result set, you may need to judge the performanc
e
impact of that.
John
> thanks for your time
> John Bell wrote:
>|||Hi,
yes, in fact each of my temporary tables strictly contains an id. Each
IF branch (now splitted into sub-procedures) fills that temp table with
the ids related to the search, then do many select queries based on
those ids.
I've change the SELECT INTO and splitted each IF into sub-procedures,
here it is now (with some commented example search at the beginning) :
--EXECUTE [DicKanjiSearch] N'=E5=8F=8B', 1, N'', 0
--EXECUTE [DicKanjiSearch] N'=E5=8B=9D', 1, N'', 1
--EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
--EXECUTE [DicKanjiSearch] N'sun', 2, N'', 1
--EXECUTE [DicKanjiSearch] N'=E3=82=B8=E3=83=A5=E3=82=A6', 3, N'=E3=81=9
8=
=E3=82=85=E3=81=86', 0
--EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N'=E3=81=9
3=
=E3=81=93=E3=82=8D', 1
--EXECUTE [DicKanjiSearch] N'3', 4, N'', 0
--EXECUTE [DicKanjiSearch] N'5', 4, N'', 1
ALTER PROCEDURE [dbo].[DicKanjiSearch] @.search nvarchar(200), @.type
tinyint,
@.search2 nvarchar(200) =3D '', @.detailed bit AS
-- @.type : 1 =3D kanji
-- 2 =3D english
-- 3 =3D hiragana or katakana : pronunciation (on'yomi / kun'yomi
/ nanori)
-- @.search2 is there in case we have to search by pronunciation, we
must
-- be able to search in hiragana and katakana. In that case,
-- on'yomi (chinese) pronunciation is in KATAKANA ans is @.search.
-- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.search2.
-- nanori (name) pronunciation is also in HIRAGANA ans therefore is
@.search2.
BEGIN
SET NOCOUNT ON
-- temp table to hold ids
CREATE TABLE #TempTable (id int PRIMARY KEY)
-- return fields for detailed listing
IF @.detailed =3D 1
BEGIN
IF @.type =3D 1
EXEC DictKanjiGetDetailedByKanji @.search
ELSE IF @.type =3D 2
EXEC DictKanjiGetDetailedByMeaning @.search
ELSE IF @.type =3D 3
EXEC DictKanjiGetDetailedByPron @.search,
@.search2
ELSE IF @.type =3D 4
This will be a new type : by stroke count
EXEC DictKanjiGetDetailedByStrokesCount
CAST(@.search as tinyint)
END
ELSE
-- return fields for basic listing
BEGIN
IF @.type =3D 1
EXEC DictKanjiGetBasicByKanji @.search
ELSE IF @.type =3D 2
EXEC DictKanjiGetBasicByMeaning @.search
ELSE IF @.type =3D 3
EXEC DictKanjiGetBasicByPron @.search, @.search2
ELSE IF @.type =3D 4
This will be a new type : by stroke count
EXEC DictKanjiGetBasicByStrokesCount CAST(@.search
as tinyint)
END
END
Now, after reading your two excellent articles (many thanks for that),
it seems that, I quote, "dynamic SQL is often the best solution, both
for performance and maintainability"...Now I have a concern with the
method I am using, because it's static SQL :| what do you think about
that?
Finally, is there a way to 'merge' queries together with the method I
am currently using? That's because I added the search type 4, which
is by kanji strokes count, I'd like to be able to search for by more
than one search type. (e.g., strokes count AND english term).
I see two ways of passing multiple parameters here. Either
1) have a @.parameter_name for each type possible (e.g.: @.meaning =3D
NULL, @.kanji =3D NULL, @.pronH =3D NULL, @.pronK =3D NULL, @.strokescount =3D
NULL, ...), then check which ones are assigned, then execute
corresponding queries.
2) use my @.search paramater as a container, like "7&name", and have my
@.type parameter something like "strokecount&meaning" (or the more
shorter "sc&en") and parse then to execute corresponding queries.
Which one would perfrom faster?
Then I still need a way to merge SELECT tables together...Any ideas?
Thank you very much!
John Bell wrote:
> Hi
> "ibiza" wrote:
>
> Yes it was a cut and paste issue! Try
> http://www.sommarskog.se/dynamic_sql.html
>
tables[vbcol=seagreen]
same or[vbcol=seagreen]
on,[vbcol=seagreen]
> Looking at the code you seem to have very similar temporary tables with
> different name. Without looking in depth it struck me that if you just
> created a single table at the begining you may be able to see more
> rationalisation. I would have expected the current client to have similar
> code to the stored procedure as you seem to be producing unique output for
> each one. This could probably be cleaned up if you had the same column na=
mes
> for everything. If you took this further and always returned the same col=
umns
> then you may be able to clean up the client coding further, the downside =
may
> be that you produce a wider result set, you may need to judge the perform=
ance[vbcol=seagreen]
> impact of that.
>
> John
tables[vbcol=seagreen]
same or[vbcol=seagreen]
on,[vbcol=seagreen]
eir own[vbcol=seagreen]
and[vbcol=seagreen]
on.[vbcol=seagreen]
n'yomi[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
I[vbcol=seagreen]
, in[vbcol=seagreen]
nue[vbcol=seagreen]
ed[vbcol=seagreen]
way[vbcol=seagreen]
AND[vbcol=seagreen]
er[vbcol=seagreen]|||Hi
The only way to see if dynamic SQL is quicker would be to implement it. In
general I would only do that if you think your current solution is too slow.
To implement multiple criteria you could separate your procedures into two
bring the second select into DicKanjiSearch, you can then call the various
procedures to add new ids to the temporary table multiple times.
John
"ibiza" wrote:
[vbcol=seagreen]
> Hi,
> yes, in fact each of my temporary tables strictly contains an id. Each
> IF branch (now splitted into sub-procedures) fills that temp table with
> the ids related to the search, then do many select queries based on
> those ids.
> I've change the SELECT INTO and splitted each IF into sub-procedures,
> here it is now (with some commented example search at the beginning) :
> --EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
> --EXECUTE [DicKanjiSearch] N'勝', 1, N'', 1
> --EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
> --EXECUTE [DicKanjiSearch] N'sun', 2, N'', 1
> --EXECUTE [DicKanjiSearch] N'ジュウ', 3, N'じゅう', 0
> --EXECUTE [DicKanjiSearch] N'ココ_', 3, N'こころ', 1
> --EXECUTE [DicKanjiSearch] N'3', 4, N'', 0
> --EXECUTE [DicKanjiSearch] N'5', 4, N'', 1
> ALTER PROCEDURE [dbo].[DicKanjiSearch] @.search nvarchar(200), @.typ
e
> tinyint,
> @.search2 nvarchar(200) = '', @.detailed bit AS
> -- @.type : 1 = kanji
> -- 2 = english
> -- 3 = hiragana or katakana : pronunciation (on'yomi / kun'yomi
> / nanori)
> -- @.search2 is there in case we have to search by pronunciation, we
> must
> -- be able to search in hiragana and katakana. In that case,
> -- on'yomi (chinese) pronunciation is in KATAKANA ans is @.search.
> -- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.search2.
> -- nanori (name) pronunciation is also in HIRAGANA ans therefore is
> @.search2.
> BEGIN
> SET NOCOUNT ON
> -- temp table to hold ids
> CREATE TABLE #TempTable (id int PRIMARY KEY)
> -- return fields for detailed listing
> IF @.detailed = 1
> BEGIN
> IF @.type = 1
> EXEC DictKanjiGetDetailedByKanji @.search
> ELSE IF @.type = 2
> EXEC DictKanjiGetDetailedByMeaning @.search
> ELSE IF @.type = 3
> EXEC DictKanjiGetDetailedByPron @.search,
> @.search2
> ELSE IF @.type = 4
> This will be a new type : by stroke count
> EXEC DictKanjiGetDetailedByStrokesCount
> CAST(@.search as tinyint)
> END
> ELSE
> -- return fields for basic listing
> BEGIN
> IF @.type = 1
> EXEC DictKanjiGetBasicByKanji @.search
> ELSE IF @.type = 2
> EXEC DictKanjiGetBasicByMeaning @.search
> ELSE IF @.type = 3
> EXEC DictKanjiGetBasicByPron @.search, @.search2
> ELSE IF @.type = 4
> This will be a new type : by stroke count
> EXEC DictKanjiGetBasicByStrokesCount CAST(@.search
> as tinyint)
> END
> END
> Now, after reading your two excellent articles (many thanks for that),
> it seems that, I quote, "dynamic SQL is often the best solution, both
> for performance and maintainability"...Now I have a concern with the
> method I am using, because it's static SQL :| what do you think about
> that?
> Finally, is there a way to 'merge' queries together with the method I
> am currently using? That's because I added the search type 4, which
> is by kanji strokes count, I'd like to be able to search for by more
> than one search type. (e.g., strokes count AND english term).
> I see two ways of passing multiple parameters here. Either
> 1) have a @.parameter_name for each type possible (e.g.: @.meaning =
> NULL, @.kanji = NULL, @.pronH = NULL, @.pronK = NULL, @.strokescount =
> NULL, ...), then check which ones are assigned, then execute
> corresponding queries.
> 2) use my @.search paramater as a container, like "7&name", and have my
> @.type parameter something like "strokecount&meaning" (or the more
> shorter "sc&en") and parse then to execute corresponding queries.
> Which one would perfrom faster?
> Then I still need a way to merge SELECT tables together...Any ideas?
> Thank you very much!
> John Bell wrote:|||Hello,

> To implement multiple criteria you could separate your procedures into two
> bring the second select into DicKanjiSearch, you can then call the various
> procedures to add new ids to the temporary table multiple times.
I don't really understand. Separating which procedures into two?
Also, multiple criteria would mean a sort of AND implemantation, so
that does not mean "add new ids to the temporary table multiple times"
(this would be an OR, am I correct?)
thanks for your time
John Bell wrote:
> Hi
> The only way to see if dynamic SQL is quicker would be to implement it. In
> general I would only do that if you think your current solution is too sl=
ow.[vbcol=seagreen]
> To implement multiple criteria you could separate your procedures into two
> bring the second select into DicKanjiSearch, you can then call the various
> procedures to add new ids to the temporary table multiple times.
> John
> "ibiza" wrote:
>
=98=E3=82=85=E3=81=86', 0[vbcol=seagreen]
=93=E3=81=93=E3=82=8D', 1[vbcol=seagreen]
mi[vbcol=seagreen]
=3D[vbcol=seagreen]
ary tables[vbcol=seagreen]
the same or[vbcol=seagreen]
nation,[vbcol=seagreen]
th[vbcol=seagreen]
ilar[vbcol=seagreen]
t for[vbcol=seagreen]
n names[vbcol=seagreen]
columns[vbcol=seagreen]
ide may[vbcol=seagreen]
formance[vbcol=seagreen]
ary tables[vbcol=seagreen]
the same or[vbcol=seagreen]
nation,[vbcol=seagreen]
o their own[vbcol=seagreen]
elds,[vbcol=seagreen]
kana and[vbcol=seagreen]
d so on.[vbcol=seagreen]
only[vbcol=seagreen]
ype[vbcol=seagreen]
/ kun'yomi[vbcol=seagreen]
, we[vbcol=seagreen]
h=2E[vbcol=seagreen]
rch2.[vbcol=seagreen]
e is[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
id[vbcol=seagreen]
201)[vbcol=seagreen]
201)[vbcol=seagreen]|||Hi
The second select statement seems to be in two flavours based on Kanji and
LessonKanji! If these are always exclusive then you may want to separate
these into two different procedures.
To get an AND condition you can select the ids where there is more than one
occurence using a group by id and a having clause, providing each time you
add the id you only insert distinct ones.
John
"ibiza" wrote:
[vbcol=seagreen]
> Hello,
>
> I don't really understand. Separating which procedures into two?
> Also, multiple criteria would mean a sort of AND implemantation, so
> that does not mean "add new ids to the temporary table multiple times"
> (this would be an OR, am I correct?)
> thanks for your time
> John Bell wrote:|||:S sorry I'd need examples to understand better what you mean.
I suppose 'the second select statement' you refer to are any select
after the
SELECT kanji_id as id
INTO #TempTable
FROM dbo.Kanjis
WHERE (condition)
into each sub-procedure? How come you say that it seems to be in two
flavours?
thanks,
Bruno
John Bell wrote:
> Hi
> The second select statement seems to be in two flavours based on Kanji and
> LessonKanji! If these are always exclusive then you may want to separate
> these into two different procedures.
> To get an AND condition you can select the ids where there is more than o=
ne
> occurence using a group by id and a having clause, providing each time y=
ou[vbcol=seagreen]
> add the id you only insert distinct ones.
> John
> "ibiza" wrote:
>
o two[vbcol=seagreen]
rious[vbcol=seagreen]
t=2E In[vbcol=seagreen]
o slow.[vbcol=seagreen]
o two[vbcol=seagreen]
rious[vbcol=seagreen]
ach[vbcol=seagreen]
with[vbcol=seagreen]
s,[vbcol=seagreen]
) :[vbcol=seagreen]
=81=98=E3=82=85=E3=81=86', 0[vbcol=seagreen]
=81=93=E3=81=93=E3=82=8D', 1[vbcol=seagreen]
n'yomi[vbcol=seagreen]
ch2[vbcol=seagreen]
arch[vbcol=seagreen]
t),[vbcol=seagreen]
th[vbcol=seagreen]
ut[vbcol=seagreen]
I[vbcol=seagreen]
=3D[vbcol=seagreen]
unt =3D[vbcol=seagreen]
my[vbcol=seagreen]
mporary tables[vbcol=seagreen]
made the same or[vbcol=seagreen]
ombination,[vbcol=seagreen]
s with[vbcol=seagreen]
just[vbcol=seagreen]
re[vbcol=seagreen]
similar[vbcol=seagreen]
utput for[vbcol=seagreen]
olumn names[vbcol=seagreen]
same columns[vbcol=seagreen]
ownside may[vbcol=seagreen]
performance[vbcol=seagreen]
mporary tables[vbcol=seagreen]
made the same or[vbcol=seagreen]
ombination,[vbcol=seagreen]
into their own[vbcol=seagreen]
t fields,[vbcol=seagreen]
ji, kana and[vbcol=seagreen]
# and so on.[vbcol=seagreen]
t is only[vbcol=seagreen]
, @.type[vbcol=seagreen]
omi / kun'yomi[vbcol=seagreen]
tion, we[vbcol=seagreen]
earch.[vbcol=seagreen]
@.search2.[vbcol=seagreen]
efore is[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
id[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
=3D id[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]|||Hi
The second select statement seems to be very similar to each other of which
there is the basic information and full information:
SELECT kanji_kanji, kanji_meaning
FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id = id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201) ASC
or
SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
lk_filename, lk_idlesson,
lesson_idlevel, lesson_idlesson,
kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_freq
FROM #KanjiIdsByKanji INNER JOIN Kanjis ON kanji_id = id
LEFT OUTER JOIN LessonKanji ON lk_idkanji = kanji_id
LEFT OUTER JOIN Lessons ON lk_idlesson = lesson_id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201) ASC
Rather than repeating this in multiple procedures they could be put at a
level where the code would only be written once (helping maintainance). If
you do this then the procedures that populate the temporary tables can just
contain single select statement and you can use INSERT.. EXEC e.g.
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
As the basic listing is a subset of the full listing, then you may want to
return the full listing result set each time and throw away the columns not
used to try and simplify the client code (although normally you would not
want to return excessively wide result sets!).
To get the 'AND' result you can do something like:
CREATE TABLE #Tmptable (id int)
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
-- And results will be have two entried one from each insert
SELECT id
FROM #Tmptable
GROUP BY id
HAVING COUNT(*) > 1
I hope that is clearer?
John
"ibiza" wrote:
[vbcol=seagreen]
> :S sorry I'd need examples to understand better what you mean.
> I suppose 'the second select statement' you refer to are any select
> after the
> SELECT kanji_id as id
> INTO #TempTable
> FROM dbo.Kanjis
> WHERE (condition)
> into each sub-procedure? How come you say that it seems to be in two
> flavours?
> thanks,
> Bruno
> John Bell wrote: