Database

hi!!!!!!! maybe you can use PIVOT[^] statements in SQL server! It's a very useful tool! good luck! ;)

No you can't. To quote the Micosoft documentation[^]: The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL)[^]. The question was regarding calling stored procedures and that is clearly not possible from within a case statement. If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles]  [My Website]

Thanks Silim it works bt now I have anoter problem when I use ArticleCount I get the Invalid column name error do you hae any idea?

Hi David, Thanks for the link above. I was wondering if you or anyone else could help with a problem I've came into with it though. I was able to encrypt the password field using the following trigger when a new record was added to the table: ALTER TRIGGER "encrypt_new_user_pwd" BEFORE INSERT ORDER 1 ON "QAS"."tableName" REFERENCING NEW AS newPwd FOR EACH ROW BEGIN Set newPwd.pwdField = ENCRYPT(newPwd.pwdField , 'key') END The problem is I'm unable to decrypt the password of the newly created record (fieldID = 0002), using the following: SELECT CAST (DECRYPT(pwdField, 'key') AS VARCHAR(100)) FROM "QAS"."tableName" WHERE fieldID = '0002' In Sybase when trying to execute the above statement I get the following error: Interactive SQL The following error occurred while fetching results: Decryption error: Input must be a multiple of 16 bytes in length for AES SQLCODE=-851, ODBC 3 State="08001" Do you have any idea what is going wrong here? The pwdField is of type VARCHAR and size 15. Thanks, Mel

You're welcome.

Do you really want to concatenate columns or just create a table? -- Table SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute FROM TableA A INNER JOIN TableB B ON A.ID = B.ID INNER JOIN TableC C ........ --or -- Concatenate SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo FROM TableA A INNER JOIN TableB B ON A.ID = B.ID INNER JOIN TableC C ........ If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places. I hope this helps, djj

Thanks for the responses. I wish I could take the time to test these, but we have decided to move on - not making this a dynamic proc.

I am assuming that you need this table only for display on your form, in which case why don't you rename the columns in the UI! Never underestimate the power of human stupidity RAH

No problem. I also don't think it was particularly dumb compared to the general par.

That makes sense. Good info, ty.

SilimSayo wrote: but you're not getting my point at all You are quite correct in that, I was only focusing on the question of the OP, not on a general discussion of how the SQL was going to interpret the statement. Never underestimate the power of human stupidity RAH

Mike654321 wrote: 55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it. That was useful info. Is it something like this you need? SELECT Table1.field2 FROM Table1 join Table2 ON Table1.field1 = Table2.field1 WHERE Table2.field2 = 9 AND Table1.field2 NOT IN ( SELECT DISTINCT Table1.field2 FROM Table1 join Table2 ON Table1.field1 = Table2.field1 WHERE Table2.field2 <> 9 ) ; This query might be very slow if the tables are big. which can be cured with composite indexes on both tables. My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.

Thanks guys. Thanks I managed to get the date saved in the following format yyyy-MM-dd and when the field is empty I simply insert NULL into the date field, which is accepted with no exception thrown.

500 records in 2 mins with no joins is very very slow. It would appear that either your server is useless or the query optimizer is unable to use indexes and is doing a table scan of all 250million records. Have you looked at the query execution plan to see if there are indexes that you can add?

I've done it! The Result is of type System.Terror :-D

Cheers - Nice:thumbsup: I am testing for speed against the other query. I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

Well you're screwed, a primary key that is dependent on your user input BBrrrttt WRONG. This is one of the most basic errors in database design. Like using a phone number or email address or ANY other user data as a PK/FK. Keys should be stupid, they should have absolutely NO other function than to maintain your data structure. Solution - create another field,BookID int with identity on your book table, everywhere you use the ISBN go there and add the new field and populate it based on existing structures (isbn). Now change you FKs to the BookID fields, now remove the FKs for the isbn. NOW you can edit your ISBN. It is a lot of work but you have to do it to correct the design error. Never put off fixing these errors, they get into the corners of your database and breed and shortly you are totally f***ed. Never underestimate the power of human stupidity RAH

well, i won't have issue saving it separately in the sql server but I don't want many controls on the form? so what's the best way to have some thing like a mask of 999/999 Left or 999/999 Right?

Try this: declare @Guid uniqueidentifier select @Guid = newid() update prices set item_guid = @Guid ... rest of code.... Tychotics: take us back to the moon "Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars." H. G. Wells