Nested

Hi all,
I have a query that looks like so:
SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID AS
Person_Name
FROM F0911
WHERE (GLAID = '00181913')

However by stipulating that GLAID = GLAID I cannot get the person_name
as not all the GLALID fields are filled in. from my reading of the
helpdesk I have a felling that a nested query might be the way to go
or a self-join but beyond this I am lost!?
Many thanks for any pointers in advance.

Sam"igloo" <igloo@.spamhole.com> wrote in message
news:eed8672e.0401080527.78d5ba30@.posting.google.c om...
> Hi all,
> I have a query that looks like so:
> SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID AS
> Person_Name
> FROM F0911
> WHERE (GLAID = '00181913')
> However by stipulating that GLAID = GLAID I cannot get the person_name
> as not all the GLALID fields are filled in. from my reading of the
> helpdesk I have a felling that a nested query might be the way to go
> or a self-join but beyond this I am lost!?
> Many thanks for any pointers in advance.
> Sam

It's not completely clear from your post what you mean - are there NULLs in
the GLALID column, or the GLAID column, or both? I've made a couple of
complete guesses below, but if they don't help then you should post some
more details, preferably including your table structure and some sample
data.

SELECT
GLDCT AS [Doc Type],
GLDOC AS DocNumber,
GLALID AS Person_Name
FROM F0911
WHERE GLAID = '00181913' OR
GLAID IS NULL

SELECT
GLDCT AS [Doc Type],
GLDOC AS DocNumber,
ISNULL(GLAID, GLALID) AS Person_Name
FROM F0911
WHERE GLAID = '00181913'

Simon|||Sorry I realise that this is somewhat esoteric I'll try and explain it
better: If I had:

Doc_TypeDoc_NumberPerson_NameGLAID

F30000181913
F300John00265898

There are many more fields but by filtering on 00181913 I could never
see the name john I need to put his name in if it has the same
Doc_Type and Doc_Number.
In an ideal world I'd like to populate the Person_Name field with all
john' but this is not practical at the present.

Hope that's a bit less muddy now?

Thanks again.
IL|||"igloo" <igloo@.spamhole.com> wrote in message
news:eed8672e.0401090717.5c9eab9c@.posting.google.c om...
> Sorry I realise that this is somewhat esoteric I'll try and explain it
> better: If I had:
> Doc_Type Doc_Number Person_Name GLAID
> F 300 00181913
> F 300 John 00265898
>
> There are many more fields but by filtering on 00181913 I could never
> see the name john I need to put his name in if it has the same
> Doc_Type and Doc_Number.
> In an ideal world I'd like to populate the Person_Name field with all
> 'john' but this is not practical at the present.
> Hope that's a bit less muddy now?
> Thanks again.
> IL

That's a little clearer, although I'm still not sure I understand
completely. But I guess you may want something like this:

select f.doc_type, f.doc_number, coalesce(f.person_name, dt.person_name),
f.GLAID
from
foo f
join
(
select distinct doc_type, doc_number, person_name
from foo
where person_name is not null) dt
on f.doc_type = dt.doc_type and
f.doc_number = dt.doc_number
where f.GLAID = '00181913'

Without knowing more about the table structure (ie the CREATE TABLE
statement), and which columns are NULLable, which are keys etc.this is just
a guess, and may not work correctly in all cases.

Simon

negative sign (unary operator) not displayed for numeric data types

I have a table with a field that has a numeric data type (15,2) and length of 9. The problem is that it won't display the actual negative sign for any values less than 0 when running a query. Any ideas? I've used Query Analyzer as well as Access.
Thanks.Huh?

DECLARE @.x decimal(15,4)
set @.x = -12345.1234
SELECT @.x|||Maybe this will help.

For instance, one customer in the table has values like this:

4380.00
4380.00
8760.00
4380.00
4380.00
23360.00
23360.00

but actually it should be:

4380.00
-4380.00
8760.00
-4380.00
-4380.00
23360.00
-23360.00

which nets to a total of 0. And if I change the query to a group query and perform a sum on this revenue field I will get 0 but if I do a normal select I'll get all positives. Sql server knows that some of the revenue is negative it just doesn't display the - sign infront of it.

Btw:

DECLARE @.x decimal(15,4)
set @.x = -12345.1234
SELECT @.x

works correctly.|||How are you viewing the data? I betcha it's a front end issue?

Or are yu seeing this in QA.

Read the huint sticky at the top of the board and post what it asks for|||"select [Value] from [YourTable] where [Value] < 0" returns what?|||I've gotten the same results using Access as the front end linking the sql server table and using Query Analzyer. I will take a look at that sticky.

If I run the same query with the < 0 criteria I will get values returned that look positive. I even copied and pasted the values into Excel and they still show up as positive values.

If I change the data type to float it works fine, but for some reason numeric data type won't display negative values.|||are you sure you aren't running this:

select abs(mycol) from mytable where mycol < 0

in any case I can't repro it:

declare @.t table (col decimal(15,2))
insert into @.t
select -12.11 union all select 234.33 union all select -44.444
select * from @.t

col
------------
-12.11
234.33
-44.44|||Sorry etaktaf, but unless you can provide us with some code for replicating this problem (create table, populate table, select results...), then I don't think we can help you any further.|||In other words post some examplkes as the hint link states

Need Transact-SQL code

Is there any transact code for sql server that I can type out to view all of the relationship Definments of the current database or even an individual table.Moving thread to T-SQL forum.|||Please take a look at the INFORMATION_SCHEMA views in SQL Server 70/2000/2005 or the system catalog views in SQL Server 2005. There are also system stored procedures like sp_helpconstraint, sp_helpindex, sp_help that will give you similar information. Search in Books Online for these SPs or views.|||

Yes. (sorry about my poor english)

Making an "simplest" answer, point to the system tables sysindexes (index), sysreferences (fk) and syscomments (views, tr, sp).

need to write from asp.net to XML type in SQL Server 2005

Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.

I have never used XML as a type in SQL Server 2005. How do we write xml into xml type.

For example the structure of XML is something like:

<application>

<applicationID = "value"></applicationID>

<customerName="value></customerName>

</application>

I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.

Kindly suggest. Thanks a lot.

You will need to look into several things to help you achieve this.

Look into the System.Xml.XmlTextWriter to help you construct XML valid strings

Also look into System.IO.StreamWriter to write the stream of data

You can then use methods of the XmlTextWriter to write the elements, attributes based on your XML structure; WriteStartDocument(), WriteStartElement(), WriteElementString();

Need to View some type of LOG file

Hi
I need to view a log of all SQL scripts that were RUN in Sequel Server.
Is It possible to view some type of a log, which will show me the script
as well as when it was run.
Many Thanks
AQ
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Retroactively, no. Going forward, you can use SQL Server Profiler to catch
scripts as they run.
Look up SQL Profiler in Books Online for information about how to use the
tool.
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
You can get the object creation date from sysobjects system table.
use dbname
go
select substring(name,1,35) as Object_name,type as Object_type,crdate from
sysobjects
Description for Object_type displayed in the above query
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Thanks
Hari
MCDBA
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!