|
SQL Statements are for extracting data from a database. As you know. IMO it is some client application which is responsible for doing somethign with that data, like displaying it, ort in your case, outputting it to a file.
Im interested however, why you think you want to do this all with a SQL statement? Something (a process, an application, maybe SQL Agent) must be executing that statement....
|
|
|
|
|
My client has a new Client that need to get this XML to be somewhere in the local drive. They are using SQl 2000 and am limited in a way. So with the above query i just need to save what is output in the local drive
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: My client has a new Client that need to get this XML to be somewhere in the local drive
So write youself a utility (Console App, Service, Windows App, VB Script) which executes your query and writes the result to the filesystem.
Easy.
|
|
|
|
|
I have a table with two columns
1- id
2- parent_id
I want a SQL query that take parent_id as perameter and retrieve all its children and children of children and so on upto N level.
Data in table like:
ID parent_id
S1 SG
G1 FG
GP01 FG
GP0101 GP01
G11 G1
G12 G1
G111 G11
G112 G11
G113 G11
If I pass FG as parent_id, the query should return G1,GP01,GP0101,G11,G12,G111,G112,G113.
Please help me in this regard
|
|
|
|
|
This kind of problem can be easily solved with Recursive CTE's.
Try this.
declare @tbl table(id varchar(20),parentid varchar(20))
insert into @tbl
select 'S1','SG' union all
select 'G1','FG' union all
select 'GP01','FG' union all
select 'GP0101','GP01' union all
select 'G11','G1' union all
select 'G12','G1' union all
select 'G111','G11' union all
select 'G112','G11' union all
select 'G113','G11'
;with cte as
(
select t1.parentid,t1.id,0 AS [Level] from @tbl t1
where t1.parentid = 'FG'
union all
select t1.parentid,t1.id,[Level]+1 from @tbl t1
inner join cte c
on c.id = t1.parentid
)
select left(Decendants,len(Decendants)-1) Decandants
from
(
select id + ','
from cte
for xml path ('')
) Result(Decendants)
Pass the parent id as a parameter from ur stored proc.
O/P:
Decandants
G1,GP01,GP0101,G11,G12,G111,G112,G113
Niladri Biswas
modified on Friday, October 30, 2009 6:11 AM
|
|
|
|
|
Niladri_Biswas wrote: Note- I sql server 2008, for solving this kind of problem you can take the advantage of Hierarchial Id's
Can 2008 use this format as a hierarchyID, there us no delimiter for the levels, the standard HID.ToString() looks like '/G12/1/'
|
|
|
|
|
Hi,
Kindly let me know how may I convert following Oracle sql into MS-Access ?
SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A, MASTER B
WHERE A.CODE=B.CODE(+)
thanx in advance
|
|
|
|
|
This should wok, I'm not sure what the significance of (+) is in the join
SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A
INNER JOIN MASTER B ON A.CODE=B.CODE
|
|
|
|
|
The (+) makes it an outer join.
|
|
|
|
|
M Riaz Bashir wrote: Kindly let me know how may I convert following Oracle sql into MS-Access ?
Why, would anyone ever move from oracle to access?
Anyway, here goes.
SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A RIGHT JOIN MASTER B
WHERE A.CODE=B.CODE
|
|
|
|
|
I think it's a left join, not a right join. I'm not an Oracle expert but I have a feeling the (+) goes on the side of the join which is not required (not sure of the technical term for that). In other words
a.code = b.code(+) is a left outer join and
a.code(+) = b.code is a right outer join.
I'm not 100% sure about that so don't take my word for it.
|
|
|
|
|
Damn!
I need a coffee. Or actually rather a beer. Or both.
|
|
|
|
|
a.code = b.code(+) The (+) says any a.code value will match a NULL b.code value. Meaning you get all the a.code values.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi
I have written a stored procedure where i am taking the select query into a variable @string and executing the query.
i am generating the query dynamically based on the SP inputs. If the query is below 4000 chars , there is no prob with it. If it exceeds then query is not executing. Kindly help....
Thanks in advance
Naina
Naina
|
|
|
|
|
If I'm right you will not be able to query/search for data/text that is larger than 4000 chars.
All column types above text(4000), like NVarChar(max) can't be a query parameter.
I hope somebody can confirm that.
Greetings
Covean
|
|
|
|
|
Hi thanks for the reply..... just did a small change by declaring as varchar(MAX) and its accepting more than 4000. Thank you
Regards
Naina
Naina
|
|
|
|
|
No problem and thank you for confirming that I'm wrong.
And good to know.
Greetings
Covean
|
|
|
|
|
You may be limited to 8000 character, the max of varchar, not sure if varchar(max) overcomes this limitation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
if i give nvarchar(8000) it says that i have crosses the linit of 4000 characters. i am using sqlserver 2005. and now i noticed that varchar(MAX) is taking only 4000 characters.
Regards
Naina
Naina
|
|
|
|
|
use varchar(8000)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i declared as varchar(8000) and assigned a query to it.
but when i tried to print the length and string, it shows only till 4000 chars and the length also its showing only 4000.
Regards
Naina
Naina
|
|
|
|
|
Hi
use nvarchar(8000)
umesh
|
|
|
|
|
The size (8000) given to the parameter '@sql' exceeds the maximum allowed (4000). is the error if i use nvarchar(8000)
Regards
Naina
Naina
|
|
|
|
|
Yes... Hightest value of NVARCHAR is 4000 and VARCHAR is 8000
BUT NVARCHAR takes large CHARSET.. So use NVARCHAR only when you need.
|
|
|
|
|
I finally got the answere.....
I declared @string as varchar(8000). But there are nvarchars declared which i am assigning to @string which finally lead to the problem . because of this @string is taking only 4000 charecters, I replaced all nvarchars with varchars and finally its taking morethan 4000 charecters.
Regards
Naina
Naina
|
|
|
|