|
Hello,
How can I escape new line or "\n" from a string in select query?
Thanking in Advance
Johnny
|
|
|
|
|
|
Hi all,
I want to split the ~ symbol from a field.so i wrote a split function ..it is wrking correctly when i give
SELECT * from [dbo].[FN_Split]('1~2','~')
1 and 2 splits correctly..
How to use the split function in a sub query??
st_id contains 1~7
SELECT * from [dbo].[FN_Split](select st_id from st_config ,'~'))
it shows the below Errors:
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
pls give me any solution ..
|
|
|
|
|
You are missing an opening bracket. And it'll die if you ever get more than one row from st_config.
|
|
|
|
|
We gave The brackets but alos it returns the same error
|
|
|
|
|
This is because of the fact that when you execute "select st_id from st_config", it gives a records set. In that case your function needs to be called for each and every rows which is not happening at present.
So you are getting this error.
Do I thing, use a loop(while) or Cursor and store the "st_id" values in a varchar variable. Next pass the variable to your split fucntion and your job will be done.
But in that case, you will get multiple records(if your original table has many columns).
Else you can do 1 more thing. While you are looping, make a string of all the values from "st_id" and place in a varchar variable.
e.g. st_id
1~2
3~4
5~6
Make it like set newstring = 1~2~3~4~5~6 (where newstring is of varchar type)
Next execute SELECT * from [dbo].[FN_Split](newstring,'~')
Hope this helps
Niladri Biswas
|
|
|
|
|
I had two tables, for Table 1 fields are contactId,Str_name and for table 2 contactId,str_phonenumber,Str_phonetype
example :
Table 1:
111 Krishna
112 Ramesh
113 Kishore
Table 2:
111 8389368936398 M1
111 3535353535353 M2
111 6326326326262 L1
111 3263262626326 L2
Now i need like this 111 Krishna 8389368936398 6326326326262
how to join this I just need any of M1 or M2 and L1 or L2 and contactid and Name how to get this. Please anyone can help me i have been strucked with this problem
Thanks
|
|
|
|
|
You need to write a procedure for getting your required output
Regards
KP
|
|
|
|
|
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'
Most likely you want to use outer join if there are no matching rows in table2.
|
|
|
|
|
Thank u very much for ur suggestion which it works
|
|
|
|
|
You're welcome
|
|
|
|
|
sir when there is no matching rows in table2, i have to retrieve the names from table1 where str_phonenumber are null. I tried with outer join getting errors please suggest me how to do
Thanks for Your Help
|
|
|
|
|
Nath wrote: I tried with outer join getting errors please suggest me how to do
What is the query like, could you post it? Also what's the error?
|
|
|
|
|
error was cleared but is not displaying record which str_phonenumber values are null
|
|
|
|
|
I don't see why str_phonenumber would affect since it wasn't part of the join. Without seeing your query, it's quite impossible to say what's wrong with it.
|
|
|
|
|
by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database
|
|
|
|
|
Nath wrote: the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you
Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.
|
|
|
|
|
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber,
m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l,trans_address_phones m
where t.int_contactid=l.int_contactid and t.int_contactid=m.int_contactid and
l.str_phonetype='L1' and m.str_phonetype='M1'
error is incorrect near the join
and I tried with single alias also
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber from
--m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l on
t.int_contactid=l.int_contactid where l.str_phonetype='L1'
here is the same problem thats the reason, whether it is possible to work with join, Help me sir
|
|
|
|
|
You're on the right track. If you modify the query like the following, do you get right results
select t.str_firstname,
t.str_lastname,
l.str_phonetype,
l.str_phonenumber
from trans_contacts t
left outer join
trans_address_phones l
on ( t.int_contactid = l.int_contactid
and l.str_phonetype = 'L1')
|
|
|
|
|
How to detect column that raises this error in PLSQL ? ORA-01401: inserted value too large for column
|
|
|
|
|
i am using oracle 9i, in which it is restricted to 'ORA-01401: inserted value too large for column' where as in orale 10g the information is provided 'ORA-12899: value too large for column "STC_USER"."DEPTTAB"."DNAME" (actual: 15,maximum: 14)'
|
|
|
|
|
First thing that comes in mind is that you catch the error using WHEN and the print out the values for example using DBMS_OUTPUT package.
|
|
|
|
|
Hi All,
I am creating an application in vb.net 2008 and Sql server 2005
I want to create a shared folder in the Server system, Than can be acceasable from all the Other System.
And This file using all the User Access Rights.
How can I create the Access Rights
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
Do not post the same question in multiple forums. This question is not an appropriate topic for this forum, and will be ignored.
|
|
|
|
|
Hello,
I'm missing something very basic with databinding to a dataset. I have a dataGridView working and updating from the UI correctly. The dataGridView also populates itself correctly from the SQL database. However, changes made in the grid persist in the grid but are not saved back to the database even on the button click. Here is the entire code. The only thing I do in a new project is add a dataGridView and a button in the designer. Then add this code to the Form1.cs file.
public partial class Form1:Form
{
private BindingSource BindingSource1 = new BindingSource();
SqlDataAdapter dataAdapterFinca = null;
DataSet dataSetFinca = null;
public Form1()
{
InitializeComponent();
this.Load += new System.EventHandler(Form1_Load);
}
protected void Form1_Load(object sender,System.EventArgs e)
{
dataGridView1.DataSource = BindingSource1;
GetData();
}
protected void GetData()
{
SqlConnection connection = new SqlConnection("MyConnectionString");
dataSetFinca = new DataSet();
dataSetFinca.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapterFinca = new SqlDataAdapter("select * from Finca",connection);
dataAdapterFinca.Fill(dataSetFinca,"Finca");
BindingSource1.DataSource = dataSetFinca;
BindingSource1.DataMember = "Finca";
}
private void button1_Click(object sender,EventArgs e)
{
if( dataSetFinca.HasChanges() )
{
this.Validate();
this.BindingSource1.EndEdit();
this.Update();
dataSetFinca.AcceptChanges();
dataAdapterFinca.Update( dataSetFinca, "Finca" );
}
}
}
|
|
|
|