UNION types date and text cannot be matched

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.
LVL 11
calinutzAsked:
Who is Participating?
 
GhitzaConnect With a Mentor Commented:
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
 
calinutzAuthor Commented:
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
 
earth man2Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
calinutzAuthor Commented:

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
 
calinutzAuthor Commented:
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
 
calinutzAuthor Commented:
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
 
calinutzAuthor Commented:
-- 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
 
earth man2Connect With a Mentor Commented:
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
 
calinutzAuthor Commented:
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
 
calinutzAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.