advice needed on T-SQL syntax

Hi folks,

I've got the following query:

SELECT ID AS HEAD_ID,
CORI,
[FROM],
TO_LIST,
CC_LIST,
BC_LIST,
SUBJECT,
PRIORITY,
MESSAGE,
ENTRY_DATE,
0 AS ISREAD
FROM EMAIL_MESSAGES WHERE (TO_LIST LIKE :TO_LIST1) AND (NOT (ID IN (SELECT 
RID FROM EMAIL_MESSAGES_DELETED WHERE FULL_NAME = :FULL_NAME1)))
AND (ID IN (SELECT RID FROM EMAIL_MESSAGES_READ WHERE FULL_NAME = 
:FULL_NAME2))
UNION
SELECT ID AS HEAD_ID,
CORI,
[FROM],
TO_LIST,
CC_LIST,
BC_LIST,
SUBJECT,
PRIORITY,
MESSAGE,
ENTRY_DATE,
1 AS ISREAD
FROM EMAIL_MESSAGES WHERE (TO_LIST LIKE :TO_LIST2) AND (NOT (ID IN (SELECT 
RID FROM EMAIL_MESSAGES_DELETED WHERE FULL_NAME = :FULL_NAME3)))
AND (NOT (ID IN (SELECT RID FROM EMAIL_MESSAGES_READ WHERE FULL_NAME = 
:FULL_NAME4)))

EMAIL_MESSAGES.MESSAGE field is of type "text"...

And when i am trying to execute, it says: The text, ntext, and image data 
types cannot be compared or sorted, except when using IS NULL or LIKE 
operator.

I tried to execute it in SQL Query tool...I changed the parameters to quoted 
strings and now the error message is: The text, ntext, or image data type 
cannot be selected as DISTINCT.

Any advice?

Thanks,
Eugene.
0
Eugene
10/22/2008 11:23:04 AM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

8 Replies
506 Views

Similar Articles

[PageSpeed] 41

> Any advice?

Hi!

It will probably work if you cast the message field to a varchar(max).

cast(MESSAGE as varchar(max)) as MESSAGE

regards

/Micke
0
Mikael
10/22/2008 12:18:42 PM
Eugene Goldberg wrote:

> Any advice?

Change UNION to UNION ALL and see if that solves the
problem.

-- 
Bill Todd (TeamB)
0
Bill
10/22/2008 1:32:39 PM
thanx Mikael, it worked! :)

"Mikael Eriksson" <micke314@gmail.com> wrote in message 
news:32545@forums.codegear.com...
0
Eugene
10/22/2008 4:19:06 PM
thanx Bill, thats it...:)

"Bill Todd" <no@no.com> wrote in message news:32593@forums.codegear.com...
0
Eugene
10/22/2008 4:19:51 PM
Actually both solutions dont work in D2007 ADO, it works in SQL Query 
though...

Any more ideas?

"Eugene Goldberg" <egold@mts-nn.ru> wrote in message 
news:32526@forums.codegear.com...
0
Eugene
10/23/2008 8:02:27 AM
Eugene Goldberg skrev:
> thanx Bill, thats it...:)

You should be aware of that there is a difference between UNION and 
UNION ALL.

UNION ALL will return all rows and UNION will remove duplicate rows.

regards

/Micke
0
Mikael
10/23/2008 1:43:18 PM
Eugene Goldberg skrev:
> Actually both solutions dont work in D2007 ADO, it works in SQL Query 
> though...
> 
> Any more ideas?

Sorry no.
I made a simple test case and that behaves as expected.

This works
{code}
ADODataSet1.CommandText :=
  'select cast(TextField as varchar(max)) '+
  'from Table1 '+
  'union '+
  'select cast(TextField as varchar(max)) '+
  'from Table1';
ADODataSet1.Open;
{code}

This works
{code}
ADODataSet1.CommandText :=
  'select TextField '+
  'from Table1 '+
  'union all '+
  'select TextField '+
  'from Table1';
ADODataSet1.Open;
{code}

This does not work
{code}
ADODataSet1.CommandText :=
  'select TextField '+
  'from Table1 '+
  'union '+
  'select TextField '+
  'from Table1';
ADODataSet1.Open;
{code}

regards

/Micke
0
Mikael
10/23/2008 1:53:42 PM
Mikael Eriksson wrote:

> Eugene Goldberg skrev:
> > Actually both solutions dont work in D2007 ADO, it works in SQL Query 
> > though...
> > 
> > Any more ideas?
> 
> Sorry no.
> I made a simple test case and that behaves as expected.
> 
> This works
> {code}
> ADODataSet1.CommandText :=
>   'select cast(TextField as varchar(max)) '+
>   'from Table1 '+
>   'union '+
>   'select cast(TextField as varchar(max)) '+
>   'from Table1';
> ADODataSet1.Open;
> {code}
> 
> This works
> {code}
> ADODataSet1.CommandText :=
>   'select TextField '+
>   'from Table1 '+
>   'union all '+
>   'select TextField '+
>   'from Table1';
> ADODataSet1.Open;
> {code}
> 
> This does not work
> {code}
> ADODataSet1.CommandText :=
>   'select TextField '+
>   'from Table1 '+
>   'union '+
>   'select TextField '+
>   'from Table1';
> ADODataSet1.Open;
> {code}
> 
> regards
> 
> /Micke

Union requires that the fields that will be return must be in the same order in
both select statements and they must have the same type and size. Now I know
that union works with ADO because I use it in a complex statement that returns
data from 5 different tables. I use the convert function as needed in order to
allow MSSQL server to execute the union corectly.

regards
Yannis.

--
0
IOANNIS
10/24/2008 11:28:19 AM
Reply:

Similar Artilces:

Need advice on IF...THEN...ELSE type T-SQL syntax
I don't write SQL that often, so... What is the most "efficient" way to write an SQL statement that accomplishes: If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information. Thanks in advance.When you ask a question, remember to click "mark as answered" when you get a reply which answers your question.My latest ASP.NET AJAX blog entries. Look into If Exists.Pushing out shapes, to a popular beat combo.  try this if exists(SELECT key_field from yourtable w...

I need to make a minor change to a SQL Server script that is in production. Need advice so I don't screw something up.
Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking forhelp is that I need to change a SQL server script that resides on one of the production database servers of thecompany I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) Thesevalues are [MO_FAX],[MO_EMAIL]. Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and quickly make the change? The big issue here is that this script is used in production. So I am just a little worrieda...

Syntax to call a t-sql script from another t-sql script
I need to know how to call a t-sql script from another t-sql script. In Oracle I can add a line: @tce_load_company.sql to a sql command file, and it will run that file. This allows me to have a single "master" file that has all of my lower level table initialization values. What is the syntax for doing this in SQLServer? Thanks, Chris Emerson "Christopher A. Emerson" wrote: > I need to know how to call a t-sql script from another t-sql script. > > In Oracle I can add a line: @tce_load_company.sql to a sql command file, and it > will r...

Documentation
Hi, I am using SQL Any for many years now (since the good old Watcom times) and it is a great (if not the best) DB server money can buy. I was also using MS SQL for quite a long time. I used to write mainly in T-SQL (because of compatibility with M$ SQL and ASE) but because of the much more powerfull language I use more and more Watcom SQL. However it would be a great help for me, to have a Watcom and T-SQL syntax displayed on the same help page. I could imagine somewhat similar to the help Microsoft provides in their SDK/API help. You can select the language on top (e.g. C...

Need advice -- test failures in t/op/pack.t
I am trying to understand the cause of test failures in t/op/pack.t on the Stratus VOS operating system. When I run pack.t in stand-alone mode, I see that tests 172, 282, 398, 502, 612, and 722 fail, all with a message similar to the following: not ok 172 # Failed at op/pack.t line 387 # For list (-128, -1, 0, 1, 127) (total -1) packed with c unpack '%53c' gave 0, expected 9.00719925474099e+15 The "packed with" clause and the unpack clause progress through "c", "i", "|", "s!", "i!", and "|!" as the tests...

Need a SQL advice
I want to show a Graph. for top 3 customer sales i made below S.Proc. SELECT C.CUSTNAME, SUM (I.SUBTOTAL) TOTAL FROM INVOICE I JOIN CUSTOMER C ON C.CUSTNO=I.CUSTNO WHERE I.TRANS='Sale' GROUP BY C.CUSTNAME ORDER BY 1 DESC ROWS 3 but i also want to show the rest of the customers for 4. rows. The result i want is CustName................Total The Rest.................2000 Gary.........................500 David........................400 Charles.....................300 Thank You "Melissa Torn" wrote in message news:548849@forums.embarcadero.com... &g...

SQL Syntax Advice
Hello Best Support Group.. Using Delphi 4, SQA 5.5.04 1867.. Attempting to get a live result set, which in Delphi means you can't use joins, normally, using a LOCATE statement in the WHERE clause fails. I use LOCATE all over the place, i've a function to help.. if lSomething then // Get only the UNEXPIRED Reserves for Standard\Detail Payment.. cListOfExpInds := PhCodeToLocateList( [ GcInd_NO ] ) else begin // Get BOTH Expired AND Unexpired for Reserve Deposit.. cListOfExpInds := PhCodeToLocateList( [ GcInd_YES, GcInd_NO ] ); end; Which builds a l...

Sybase T-SQL and MS T-SQL
Guys is there any syntax difference in Sybase Transact SQL and MS SQL Server Transact SQL . I have good experience coding stored procs on Sybase . Do I need to go through MS manuals . Any response is highly appreciated. Yes. The later the version of Mssql the more extensions they add. If you want to get the most out of it pick up one of the many T-sql books available for MSSQL. joe <Raj_zutshi> wrote in message news:546B13E8283CAF1B004B3E1885256A8B.004B3E2B85256A8B@webforums... > Guys is there any syntax difference in Sybase Transact SQL and MS SQL > Server Tran...

ASA T-SQL vs MS T-SQL
Does anyone have/know of a list of points that present the differences between Sybase Adaptive Server Anywhere Transact SQL and Microsoft's T-SQL? This information would be used for porting some MS T-SQL to a SQL Anywhere 7 database. >Does anyone have/know of a list of points that present the differences >between Sybase Adaptive Server Anywhere Transact SQL and Microsoft's >T-SQL? >This information would be used for porting some MS T-SQL to a SQL >Anywhere 7 database. The ASA online help has an lengthy section on ASA (watcom) sql to T-SQL compatabili...

Need Help with SQL Syntax
Hi, Excuse me If this question is a little simple. Also, please let me know if this is not the appropriate newsgroup and which one would be. I am trying to display only the products under a specific category number and display the product information and stock level. But I can't seem to get the query to work. (I get no error in my log) . Is my SQL syntax right? (The Perl is correct and the SQL works if I exclude one of the rows from the query i.e the Stock level) Any help is appreciated. Thanks, Luis Martinez ------- Here is the Table Structure-------- nr = number...

Need some help with T-SQL
Hi, I'm new to t-sql and just need some help writing a query for a project:Here's what I need to do: I have a table with about 3,000,000 rows in it, so I'd like to query what I need from it into a temporary table. Here's the sql I need to use to fill this table:select AccessID, ClientIP, ResourceAccessed, Server, TimeStamp, UserAgent, UserID, UserName from accesslogtest where resourceaccessed like '%articledetail%' or resourceaccessed like '%default%' order by resourceaccessed, clientIP, username, timestamp desc Then, I need to go through each row and com...

ASA advice needed for Delphi
Hello, We are using Delphi 5, and have decided to skip the BDE layer, when connecting to ASA. We need advice to select which 3rd party library is fastest and offers the best interface to ASA. So far we have found 3 candidates that replaces the BDE: 1) Titan 2000 2) NativeDB for SQL Anywhere 3) ODBC Express Does anyone have experience with any of these, and could advice us on selecting which will fullfill our needs best? regards L.Zienka why do you want replace the BDE, have you trouble?? because I use the BDE for my application do you have success to create your da...

Need advice on a sql calculation
I have a system that tracks scores. Due to various rules of the game, to calculate each person's score I have to: 1. select all records (scores) belonging to the person 2. sort them by date 3. inspect them one by one for a variety of criteria 4. take the remaining records (scores) and sort them by the score 5. add up the top 5 to find the persons actual score. whew! Right now it is working but by doing various sql's, creating temp tables and inserting records in the temp table so I can then sort them, etc. Very very time consuming. I now this is really vague, but i...

Need more T-SQL functionality
Sybase, can we get more T-SQL functionality ie user defined functions that can be used in store procs as highest priority and other "T-SQL" which is MS SQL or Oracle and not in Sybase? GG wrote: > Sybase, > can we get more T-SQL functionality ie user defined > functions that can be used in store procs as highest > priority and other "T-SQL" which is MS SQL or Oracle and not > in Sybase? User defined functions can since version 12.0 be written in Java. See: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_12.5.1.java/html/...

Web resources about - advice needed on T-SQL syntax - embarcadero.delphi.ado

Advice - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Agile Advice – Working With Agile Methods (Scrum, OpenAgile, Lean) - All Things Agile: Scrum, OpenAgile ...
Each team member thinks about how much effort the team will expend to fully complete all the work for the item. Comparing this work to the work ...

Advice On Facebook Timeline Pages: Covers, Content
Marketers and brand managers are still digesting yesterday's news about the launch of timeline for pages, and social media marketing platform ...

Jobs & Job Search Advice, Employment & Careers - Careerbuilder.com
Looking for a new job? Get advice or search over 1.6 million jobs on the largest job site, set alerts to be first in line and have new jobs emailed ...

Some advice for the world's no. 4
Some advice for the world's no. 4.

Facebook’s holiday advice to retailers: go mobile
As retailers get into the meat of the all-important Q4 , Facebook published some tips for advertisers looking to boost sales in the holiday season ...



iOS 7 Advice Line (@iOS7AdviceLine) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Travel Advice (@traveladvice) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Resources last updated: 12/21/2015 9:47:24 PM