|
We prefer not to spoon-feed people here.
|
|
|
|
|
Hello All,
I am using 2 databases. In one database I have table which has a column named PNumber and this column has data something like this :
7,189,413
7,196,205
7,202,331
7,217,254
In another database same column name is there but data is something like this:
7189413
7196205
7202331
7217254
I need to join these two column but I am not able to get the required data.
Please help me...
Thanks in advance....
modified on Monday, August 11, 2008 7:18 AM
|
|
|
|
|
I am assuming that the data type is not a numeric one, otherwise your join would work, so try
where replace(Pnumber,',','') = Pnumber
This will strip the commas out.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the reply, but its not working.
I want Expiry Date data but its not showing date. All the values in that column is Null but data is there in database...
|
|
|
|
|
Ok, post the table definitions and your query and I'll try to help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hey thanks a lot for the reply and help. Its working fine. I just used replace where I was doing join. Something like this:
SELECT CONVERT(nvarchar(12), b.pexpiry_date, 101) as Expiry_Date FROM Table_A a
left join Table_B b with(NoLock)
on replace(a.pno,',','') = b.pnumber
Thanks again... 
|
|
|
|
|
Good, thats what I meant
Bob
Ashfield Consultants Ltd
|
|
|
|
|
DECLARE @value AS VARCHAR(255)<br />
<br />
SET @value='78,567,9'<br />
<br />
SELECT REPLACE(@value,',','')
I see you have already soultion but I hope you don't mind for my answer.
Regards.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Good Morning All
I have been Running a Stored Procedure from another Server and it Crashed,Am restoring my Application to a new Server. i have restored my DB's into that SQL Server and my SP's are there and my Application complains that the SP's were not Found. What i did i recompiled the SP's and try again from the Management Studio to run the Proc itself not from the App and it gave me the same Error. e.g here is what i did
Alter Procedure [sde].[PrcSelect_lock_Pro_View]
(
@Lis_key varchar(50)
)
as
SELECT Primary_ID,
Boundary_area,
Attrib_Code,
Lis_Key,
Func_Key,
Actual_Extent,
Improved_Value,
Site_Value,
Purchase_price,
Purchase_date,
prop_id,
confidence_score,
fin_ls_prediction,
new_lis_key,
new_func_key,
Tshwane_Prediction,
matchflag,
source,
flagtype,
Locked,
New,
Country
FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key)
AND (Locked IS NULL)
Order by Lis_key,Func_key
Update Property_Mass
set Locked = 1
WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked IS NULL)
When i run this code it execute fine , but when you run the PS and pass the Param then it says
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'PrcSelect_lock_Pro_View'
Even if i see the SP.
What could be a Problem.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Are you specifying the correct owner ([sde].) when you run the SP?
|
|
|
|
|
Thanks it worked.
i was supposed to use the sde user.
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Iam facing a problem with Postgres 8.1.4 Windows native version.
I am writing a simple application that executes an update query in a postgres database.
This query execution will be called in a loop for 500 times.
After starting the execution of the exe, I am switching off the Postgres server machine (say abrupt shutdown of the machine happened).
In this scenario, my sample application hangs.
Application code snippet would be like the below.
DBObj.Open (NULL, FALSE, FALSE, ConnectionString);
SQL = "Update mytable set sno=1 where sno=1";
for(int n=0; n <500; n++)
{
Try
{
WriteLog(“Start”);
DBObj.ExecuteSQL (strSql);
WriteLog(“End”);
}
Catch Exceptions…{}
}
Application hangs in the ExecuteSql statement itself.
This problem is not happening with Postgres 7.2.4 that runs on top of Cygwin.
And also this problem is not happening if I shutdown the server alone from Services
Instead of machine shutdown.
Any help is appreciated.
Thanks in advance.
|
|
|
|
|
I would have thought that the executesql would return an error if the server dissapears!. Presumably it never gets into the catch exceptions area!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ya ,
The timeout is not happening any way to solve the problem?
|
|
|
|
|
So no error, no reaction, how does the app crash! I'm clutching at straws here it feels like stepping onto a non existent step!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No it doesn't crash the ExecuteSQL doesn't return and the appilcation hangs /.
|
|
|
|
|
krishnan, I'm now going to ignore this thread and hope it goes away, sorry I have no idea how you are going to deal with this problem.
Idea, does it show the same behaviour in the release and debug versions. Huh oh damm the straw got away...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Do you have a settable property for CommandTimeout as in ADO.NET? Setting that might solve the problem.
If not, you may have to run a dummy query like Select GETDATE() before you run your actual query to check if the DB server is up and running.
|
|
|
|
|
Executing that query also invloves executesql right?
It will also hang in that statement 
|
|
|
|
|
My WinForms application runs with SQLite database and I am very satisfied with it. Now I need to add export/import feature to my application, so I need also data format for these export files.
First I thought that XML or just some binary format could work, but then I realized that I could use directly some small SQLite database file. The export database file could contain only a single table with export data, or it could even be a part of the main database with the same format.
Do you recommend to use directly a database file for exporting/importing of data? It would be probably easier for me to work with SQLite format, compared to another format. Users will not need to edit the export files in any way, and the data will be always imported only back to my application, possibly located on another machine.
Thanks,
Petr
|
|
|
|
|
You are not describing and Export/Import functionality as this implies you are exporting from your application for use elswhere. This requires an open format usually CSV for large volumes or XML if the volume is not large.
Your requirement of transferring data between 2 versions of YOUR app is a different animal altogether. Use the tool your are most familiar with. Also if it is a sQLLite db it is less open to the user, therefore safer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry, I dind't point out that the export/import feature will serve only to transfer data between two copies of my app, so the SQLite format will be probably the best bet.
|
|
|
|
|
Actually you did point out the requirement, I was just being pedantic. SQLLite is by far the best solution for this requirement!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am starting a C# Winforms application where part of the main data entry form consists of seven comboboxes allowing the user to select a single item from each combobox, or leave the column blank if they so choose. I am representing the choices in the comboboxes as text, of course, but in my main table I will store the unique ID (int) that they choose.
I want the user to be able to maintain the lists that populate the seven comboboxes. What is the best database design for doing this? I have tried two different methodologies so far. First I created seven tables, one for each combobox, since each one has a different meta data type. The tables had three columns - ID, Name, and Description. The ID was the primary key that I related to the main table.
Then I thought it would be better to combine all seven lists into one "meta data" table by simply adding a TYPEID column to the table. That made designing the form to maintain the meta data lists a bit easier. However, when I try to relate my "metadata" table to my main table ON MULTIPLE COLUMNS AND enforce referential integrity I get the following error in the SQL Designer and the VS Dataset Designer:
" Introducing FOREIGN KEY constraint 'FK_docs_status' on table 'docs' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. "
What is the best design pattern? Should I maintain seven different "metadata" tables?
Thank you.
Elgin Rogers
modified on Sunday, August 10, 2008 9:15 AM
|
|
|
|
|
Hi,
In my opinion, your approach with only single "metadata" is better that seven different ones. What about the foreign keys? From your error message I understand that Status is a "metadata" table and docs is an actual data table.
How does the Docs refers to Status? If the constraint is set to ON DELETE CASCADE, do you really want that when a status is deleted, docs which have the corresponding status are also deleted?
If the problem is with update (ON UPDATE CASCADE), I think you shouldn't use keys that are updatable, but instead use surrogate keys.
Also does the main table have seven foreign key columns, one for each choice user makes. This error also occurs if you use the same column for two different foreign keys.
Mika
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
