Solved

UNION types date and text cannot be matched

Posted on 2006-06-26
10
1,561 Views
Last Modified: 2012-06-21
I have a problem running this UNION query...
Anyone ?


select numeasig, nr, codasig1, seriech||op as opch, datapl as dataplch, nrrata as nrrataCH, NULL as opop, NULL as dataplop, NULL as nrrataOP, primainc as cuChit_int, valuta as valuta, NULL as cuChit_rata,
NULL as valuta, NULL as cuOP_int, NULL as valuta, NULL as cuOP_rata, NULL as valuta from borddtl where idx=10 and tipdocpl = 'CH' and nrrata=''
union
select numeasig, nr, codasig1, seriech||op, datapl, nrrata, NULL, NULL, NULL, NULL, NULL, primainc, valuta, NULL, NULL, NULL, NULL  from borddtl where idx=10 and tipdocpl = 'CH' and nrrata<>''
union
select numeasig, nr, codasig1, NULL, NULL, NULL, seriech||op, datapl, nrrata, NULL, NULL, NULL, NULL, primainc, valuta, NULL, NULL  from borddtl where idx=10 and tipdocpl = 'OP' and nrrata=''
union
select numeasig, nr, codasig1, NULL, NULL, NULL, seriech||op, datapl, nrrata, NULL, NULL, NULL, NULL, NULL, NULL, primainc, valuta from borddtl where idx=10 and tipdocpl = 'OP' and nrrata<>''
order by nr,numeasig,codasig1,datapl


It is kinda urgent, so please help me out with this problem.
0
Comment
Question by:calinutz
  • 7
  • 2
10 Comments
 
LVL 11

Author Comment

by:calinutz
ID: 16990742
Hello....
anybody....?

I'm trying to build a result set from these selects by union-ing them
Of course I'll use UNION ALL instead of simple UNION. But this is not the problem. The problem is this PostgreSQL error...
I do not get any error when running this Query in an SQL Server 2000 -2005 and the syntax seems fine to me... But it seems like Postgres has other rules, rules that I cannot seem to find in the documentation.

What does this mean anyways? I do not try to match date and text fields... so what is this?
And how can I solve it?

Please help me with this.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 16991604
What is the postgresql error ?

It could be NULL string behaviour which SQLServer deviates from the ANSI SQL standard.
Try changing nrrdata='' to nrrdata is null
                    nrrdata<>'' to nrrdata is not null
0
 
LVL 11

Author Comment

by:calinutz
ID: 16991842

ERROR:  UNION types text and date cannot be matched

Strange is that if I try UNION-ing 3 of them (instead of 4) randomly, some work, some give the same error.
It is very strange behaviour
Please any ideea?
0
 
LVL 11

Author Comment

by:calinutz
ID: 16991904
SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL,NULL,NULL,primainc,valuta,0,NULL,0,NULL,0,NULL from borddtl where idx=12 and tipdocpl = 'CH' and nrrata is NULL
UNION ALL
SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL,NULL,NULL,0,NULL,primainc,valuta,0,NULL,0,NULL from borddtl where idx=12 and tipdocpl = 'CH' and nrrata is not NULL
UNION ALL
SELECT numeasig,nr,codasig1,NULL,NULL,NULL,op, datapl, nrrata, 0, NULL, 0, NULL, primainc,valuta, 0, NULL from borddtl where idx=12 and tipdocpl = 'OP' and nrrata is NULL
UNION ALL
SELECT numeasig,nr,codasig1,NULL,NULL,NULL,op, datapl, nrrata,0,NULL,0, NULL, 0, NULL, primainc,valuta from borddtl where idx=12 and tipdocpl = 'OP' and nrrata is not NULL


I recreated the Query... step by step... Another strange thing is that if I start doing the union using just a few fields (including the datapl field that is of type date) the UNION works, but after I add the 14th field to the query this error occures

The only date dield is datapl, the others are as follows:
primainc   :numeric(19,4)
valuta       :char(3),
nrrata       :varchar(2),
codasig     :varchar(60)
op            :varchar(25)
seriech     :varchar(10)
numeasig  :varchar(100)
nr             :varchar(7)

So the error seems to have something against combining date with text... but where do I do that? I don't understand. And naturally it is not the only UNION error I received while building this query. I recieved an error like this too:
ERROR:  UNION types int and text cannot be matched

What is wrong with this query?
0
 
LVL 11

Author Comment

by:calinutz
ID: 16991923
It should be so easy... they are SELECT statements on a single table, united with UNION... what is wrong here?
Do you need my exact table?
Because I could paste you the DDL...
Actually I will paste it and please try to help me, because this is really stupid.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 11

Author Comment

by:calinutz
ID: 16991925
-- Table: borddtl

-- DROP TABLE borddtl;

CREATE TABLE borddtl
(
  id int8 NOT NULL DEFAULT nextval(('"public"."borddtl_id_seq"'::text)::regclass),
  bord varchar(15),
  serie char(4),
  nr char(7),
  bordan int4,
  socasig char(3),
  op varchar(60),
  datapl date,
  nrrata varchar(6),
  supliment char(21),
  nrcv char(7),
  nrinm varchar(50),
  numeasig varchar(100),
  adresaasig varchar(70),
  codasig1 varchar(50),
  codasig2 varchar(50),
  codasig3 varchar(50),
  codasig4 varchar(50),
  codasig5 varchar(50),
  codrisc char(2),
  sumaasig numeric(19,4),
  v char(10),
  primasubscr numeric(19,4),
  valutasubscr varchar(3),
  primainc numeric(19,4),
  valuta char(10),
  dela date,
  panala date,
  cod char(21),
  rel int4,
  crca varchar(10),
  agent char(3),
  codrisc2 char(2),
  codrisc3 char(2),
  codcomplex varchar(5),
  datacurs date,
  cvnr varchar(7),
  cvcod varchar(21),
  cvdela date,
  cvpanala date,
  cvauto varchar(15),
  dataem date,
  idx int4,
  telasig varchar(30),
  acapac int4,
  amasa int4,
  anrloc int2,
  aputere int4,
  asasiu varchar(40),
  afel varchar(30),
  amarca varchar(50),
  atip varchar(50),
  utilizator varchar(50),
  uadresa varchar(70),
  ucnp varchar(50),
  utel varchar(30),
  cvan int4,
  crcacod varchar(25),
  denobj varchar(90),
  crcaserie varchar(4),
  crcaagent varchar(3),
  cvserie varchar(4),
  tipdocpl char(3),
  seriech varchar(7),
  idch int4,
  vigneta varchar(10),
  vignetacod varchar(50),
  procb numeric(19,4),
  proca numeric(19,4),
  comisa numeric(19,4),
  comisb numeric(19,4),
  CONSTRAINT borddtl_pkey PRIMARY KEY (id),
  CONSTRAINT fk_borddtl_bordmst FOREIGN KEY (idx)
      REFERENCES bordmst (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE borddtl OWNER TO calinutz;


-- Index: "IX_borddtl"

-- DROP INDEX "IX_borddtl";

CREATE INDEX "IX_borddtl"
  ON borddtl
  USING btree
  (bord);

-- Index: "IX_borddtl_1"

-- DROP INDEX "IX_borddtl_1";

CREATE INDEX "IX_borddtl_1"
  ON borddtl
  USING btree
  (serie);

-- Index: "IX_borddtl_10"

-- DROP INDEX "IX_borddtl_10";

CREATE INDEX "IX_borddtl_10"
  ON borddtl
  USING btree
  (codasig1);

-- Index: "IX_borddtl_11"

-- DROP INDEX "IX_borddtl_11";

CREATE INDEX "IX_borddtl_11"
  ON borddtl
  USING btree
  (cod);

-- Index: "IX_borddtl_12"

-- DROP INDEX "IX_borddtl_12";

CREATE INDEX "IX_borddtl_12"
  ON borddtl
  USING btree
  (rel);

-- Index: "IX_borddtl_13"

-- DROP INDEX "IX_borddtl_13";

CREATE INDEX "IX_borddtl_13"
  ON borddtl
  USING btree
  (agent);

-- Index: "IX_borddtl_14"

-- DROP INDEX "IX_borddtl_14";

CREATE INDEX "IX_borddtl_14"
  ON borddtl
  USING btree
  (cvcod);

-- Index: "IX_borddtl_15"

-- DROP INDEX "IX_borddtl_15";

CREATE INDEX "IX_borddtl_15"
  ON borddtl
  USING btree
  (cvnr);

-- Index: "IX_borddtl_16"

-- DROP INDEX "IX_borddtl_16";

CREATE INDEX "IX_borddtl_16"
  ON borddtl
  USING btree
  (crcacod);

-- Index: "IX_borddtl_17"

-- DROP INDEX "IX_borddtl_17";

CREATE INDEX "IX_borddtl_17"
  ON borddtl
  USING btree
  (tipdocpl);

-- Index: "IX_borddtl_2"

-- DROP INDEX "IX_borddtl_2";

CREATE INDEX "IX_borddtl_2"
  ON borddtl
  USING btree
  (nr);

-- Index: "IX_borddtl_3"

-- DROP INDEX "IX_borddtl_3";

CREATE INDEX "IX_borddtl_3"
  ON borddtl
  USING btree
  (bordan);

-- Index: "IX_borddtl_4"

-- DROP INDEX "IX_borddtl_4";

CREATE INDEX "IX_borddtl_4"
  ON borddtl
  USING btree
  (idx);

-- Index: "IX_borddtl_5"

-- DROP INDEX "IX_borddtl_5";

CREATE INDEX "IX_borddtl_5"
  ON borddtl
  USING btree
  (socasig);

-- Index: "IX_borddtl_6"

-- DROP INDEX "IX_borddtl_6";

CREATE INDEX "IX_borddtl_6"
  ON borddtl
  USING btree
  (datapl);

-- Index: "IX_borddtl_7"

-- DROP INDEX "IX_borddtl_7";

CREATE INDEX "IX_borddtl_7"
  ON borddtl
  USING btree
  (nrcv);

-- Index: "IX_borddtl_8"

-- DROP INDEX "IX_borddtl_8";

CREATE INDEX "IX_borddtl_8"
  ON borddtl
  USING btree
  (nrinm);

-- Index: "IX_borddtl_9"

-- DROP INDEX "IX_borddtl_9";

CREATE INDEX "IX_borddtl_9"
  ON borddtl
  USING btree
  (numeasig);

0
 
LVL 3

Accepted Solution

by:
Ghitza earned 350 total points
ID: 16992946
Try this... works for me :)

SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL::text,NULL::date,NULL::text,primainc,valuta,0,NULL::text,0,NULL::text,0,NULL::text
from borddtl where idx=12 and tipdocpl = 'CH' and nrrata is NULL
UNION ALL
SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL::text,NULL::date,NULL::text,0,NULL::text,primainc,valuta,0,NULL::text,0,NULL::text
from borddtl where idx=12 and tipdocpl = 'CH' and nrrata is not NULL
UNION ALL
SELECT numeasig,nr,codasig1,NULL::text,NULL::date,NULL::text,op, datapl, nrrata, 0, NULL::text, 0, NULL::text, primainc,valuta, 0, NULL::text
from borddtl where idx=12 and tipdocpl = 'OP' and nrrata is NULL
UNION ALL
SELECT numeasig,nr,codasig1,NULL::text,NULL::date,NULL::text,op, datapl, nrrata,0,NULL::text,0, NULL::text, 0, NULL::text, primainc, valuta
from borddtl where idx=12 and tipdocpl = 'OP' and nrrata is not NULL
0
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 150 total points
ID: 16993906
You should only need to specify the column type in the first select ...
SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL::text,NULL::date,NULL::text,primainc,valuta,0,NULL::text,0,NULL::text,0,NULL::text
from borddtl where idx=12 and tipdocpl = 'CH' and nrrata is NULL
UNION ALL .....
0
 
LVL 11

Author Comment

by:calinutz
ID: 16994988
Thank you both. You really helped me with this one.
Ghitza had the answer and earthman2 put the cherry on top of the solution.
Thanks again.
0
 
LVL 11

Author Comment

by:calinutz
ID: 16995018
Anyway for other people that has this problem and maybe uses PostgresDAC combined with delphi you will notice that PostgresDAC does not allow the expression " ::text ". I avoided this problem by declaring a function that does the declaration returning varchar, and called from delphi the function instead of the above select syntax. So instead of

SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,NULL::text,....
i used
SELECT numeasig,nr,codasig1,seriech||op,datapl,nrrata,totext(NULL),...

where totext is a function declared by me that returns
select $1::text


(same goes for ::date  ==> todate(NULL))

Thank you again
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now