Column headers and concatenation

[alexhu]

I have a script

select ordernr||'|'|| ordstatcd||'|'|| clientordref||'|'|| custordref||'|'|| soheadref1||'|'|| soheadref2||'|'||
collection\$acccd||'|'||  RECIPIENT\$ACCCD||'|'|| carrcd||'|'|| to_char(delivery\$date, 'DD-MON-YYYY HH24:MI')||'|'|| 
to_char(delivery\$time, 'DD-MON-YYYY HH24:MI')||'|'||
personnel||'|'|| depotcd||'|'|| rtcd||'|'|| ordersource||'|'|| completedyn||'|'|| itinnr||'|'|| consignmentid ||'|' from sohead;
[code]

this produces the output

ORDERNR||'|'||ORDSTATCD||'|'||CLIENTORDREF||'|'||CUSTORDREF||'|'||SOHEADREF1||'|'||SOHEADREF2||'|'||COLLECTION                      
                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
182|NEW|182||5500013791|0000000068|0000084050|8408||18-DEC-2002 00:00|18-DEC-2002 00:00|SYSTEM|8408|AT ORDER|EDI|0|1|22|            
                                                                                                                            
181|NEW|181||5500012141|0000000107|0000084050|8408||18-DEC-2002 00:00|18-DEC-2002 00:00|SYSTEM|8408|AT ORDER|EDI|0|1|2|             
                                                                                                                            
183|NEW|183||5500016742|0000000034|0000084050|8408||18-DEC-2002 00:00|18-DEC-2002 00:00|SYSTEM|8408|AT ORDER|EDI|0|1|22|            


which is required for our EDI process


I now have to modify this script to include only lines from the last 62 days, so have to join the table to others like this

[code]
  select a.ordernr ordernr ||'|'|| a.ordstatcd ||'|'||
  a.clientordref ||'|'|| a.custordref ||'|'||
  a.soheadref1 ||'|'|| a.soheadref2 ||'|'||
  a.collection$acccd ||'|'||
  a.RECIPIENT$ACCCD ||'|'||
  a.carrcd  ||'|'||
  to_char(a.delivery$date, 'DD-MON-YYYY HH24:MI') ||'|'||
  to_char(a.delivery$time, 'DD-MON-YYYY HH24:MI') ||'|'||
  a.personnel ||'|'|| a.depotcd ||'|'|| a.rtcd ||'|'||
  a.ordersource  ||'|'|| a.completedyn ||'|'||
  a.itinnr ||'|'|| a.consignmentid
  from sohead a, itindetail b, itinchklist c
  where b.projected$datestartltz > (sysdate - 62)
  and
  c.itinno=b.itinnr
  and
  a.consignmentid=c.consignmentid
[code]

and this gives the output as before with the correct rows.

However the EDI software does not like the a.<column> b.<column etc

Changing it in the normal method "a.column alias" does not work in this case

Anyone know how to do this ?

Alex

 

[alexhu]

Hmmm....... must practice more with TGML tags :-(

Alex

 

[zephan]

Since your output is a single column, why not alias it using a static double quoted string :

  select a.ordernr ordernr ||'|'|| a.ordstatcd ||'|'||
  a.clientordref ||'|'|| a.custordref ||'|'||
  a.soheadref1 ||'|'|| a.soheadref2 ||'|'||
  a.collection$acccd ||'|'||
  a.RECIPIENT$ACCCD ||'|'||
  a.carrcd  ||'|'||
  to_char(a.delivery$date, 'DD-MON-YYYY HH24:MI') ||'|'||
  to_char(a.delivery$time, 'DD-MON-YYYY HH24:MI') ||'|'||
  a.personnel ||'|'|| a.depotcd ||'|'|| a.rtcd ||'|'||
  a.ordersource  ||'|'|| a.completedyn ||'|'||
  a.itinnr ||'|'|| a.consignmentid
  [b]"ORDERNR||'|'||ORDSTATCD||'|'||CLIENTORDREF||'|'||
  CUSTORDREF||'|'||SOHEADREF1||'|'||SOHEADREF2||'|'||COLLECTION"[/b]
  from sohead a, itindetail b, itinchklist c
  where b.projected$datestartltz > (sysdate - 62)
  and
  c.itinno=b.itinnr
  and
  a.consignmentid=c.consignmentid

The only problem is that it is static so every at change in the query you have to change the alias.

Note : enclosing tag must be preceded by a slash [/code]

Regards,
Zephan

 

[alexhu]

Zephan

thanks for the answer - the new code comes up with 'identifier too long' for the new string, so I have got around the problem bu uing a UNIX utility outside of the database (the output is a repotr)

Thanks

Alex

 

[zephan]

Ooops, sorry, not tested before posting.