Forum Stats

  • 3,770,497 Users
  • 2,253,126 Discussions
  • 7,875,486 Comments

Discussions

LISTAGG result conversion for MS SQL.

Mameli75
Mameli75 Member Posts: 8 Green Ribbon

Hello,

I've found useful function LISTAGG below to get all email addresses (t$info) in one row ";" separated, ready to use and I can see expected results in oracle SQL developer BUT when I call this as a view by MS SQL Link server I do not see any data for that column. I know it could be an issue by MS SQL conversion but I think it's related to the big column type and size list agg generate automatically (varchar2 4000 char). So I'm asking how to convert result in a more compatible way such like NVARCHAR2(). Thanks.

SELECT cnbp.t$bpid bpid,

    LISTAGG(t$info, ';') WITHIN GROUP (ORDER BY cnbp.t$bpid) info

FROM baan.ttccom145xxx cnbp, baan.ttccom140xxx cnt

where cnbp.t$ccnt = cnt.t$ccnt

and cnbp.t$cmsk$4 = 1 -- (only pay-by)

GROUP BY cnbp.t$bpid

order by 1 desc

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 693 Silver Trophy

    Try to cast the type

    cast(listagg(...) as varchar2(2000 char))

    or

    cast(listagg(...) as nvarchar2(2000))

    Mameli75
  • Mameli75
    Mameli75 Member Posts: 8 Green Ribbon

    tried ... but it "get lost" any data when select by MS SQL ... better say I cannot see it but something is there

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    edited Jun 16, 2021 11:47PM

    I still don't understand what are you trying to do? It looks like you are querying Oracle from SQL Server via linked server. If so, you should get an error if listagg returns over 4000 bytes:

    I:\>sqlcmd -S some_server -d some_db -h -1
    1> -- listagg against 5 tables
    2> SELECT * FROM OPENQUERY([some_linked_server] ,'select listagg(table_name,'','') within group(order by table_name) from all_tables where rownum <= 5')
    3> go
    CON$,FILE$,ICOL$,PROXY_ROLE_DATA$,UNDO$
                                                                                     
    (1 rows affected)
    1> -- now we will do listagg against all tables
    2> SELECT * FROM OPENQUERY([some_linked_server] , 'select listagg(table_name,'','') within group(order by table_name) from all_tables')
    3> go
    Msg 7320, Level 16, State 2, Server some_server, Line 2
    Cannot execute the query "select listagg(table_name,',') within group(order by table_name) from all_tables" against OLE DB provider "OraOLEDB.Oracle" for linked server "some_linked_server".
    OLE DB provider "OraOLEDB.Oracle" for linked server "some_linked_server" returned message "ORA-01489: result of string concatenation is too long".
    OLE DB provider "OraOLEDB.Oracle" for linked server "some_linked_server" returned message "ORA-01489: result of string concatenation is too long".
    1>
    

    Depending on Oracle version you can truncate overflow:

    1> SELECT * FROM OPENQUERY([some_linked_server] , 'select listagg(table_name,'','' on overflow truncate) within group(order by table_name) from all_tables')
    2> go
    ACCESS$,ACL$_OBJ,ACLMV$,ACLMV$_REFLOG,ACLMVREFSTAT$,ACLMVSUBTBL$,ADMINAUTH$,ADO_IMPARAM$,ADO_IMSEGSTAT$,ADO_IMSEGTASKDETAILS$,ADO_IMSTAT$,ADO_IMTASKS$,ALERT_QT,ALL_CHUNKS,ALL_UNIFIED_AUDIT_ACTIONS,APPLY$_AUTO_CDR_COLUMN_GROUPS,APPLY$_BATCH_SQL_STATS,APPLY$_CDR_INFO,APPLY$_CHANGE_HANDLERS,APPLY$_CONF_HDLR_COLUMNS,APPLY$_CONSTRAINT_COLUMNS,APPLY$_COORDINATOR_STATS,APPLY$_DEST_OBJ,APPLY$_DEST_OBJ_CMAP,APPLY$_DEST_OBJ_OPS,APPLY$_ERROR,APPLY$_ERROR_HANDLER,APPLY$_ERROR_TXN,APPLY$_PROCEDURE_STATS,APPLY$_READER_STATS,APPLY$_SERVER_STATS,APPLY$_SOURCE_OBJ,APPLY$_SOURCE_SCHEMA,APPLY$_TABLE_STATS,APPLY$_VIRTUAL_OBJ_CONS,APPROLE$,APP_ROLE_MEMBERSHIP,APP_USERS_AND_ROLES,AQ$_ALERT_QT_G,AQ$_ALERT_QT_H,AQ$_ALERT_QT_I,AQ$_ALERT_QT_L,AQ$_ALERT_QT_S,AQ$_ALERT_QT_T,AQ$_AQ$_MEM_MC_G,AQ$_AQ$_MEM_MC_H,AQ$_AQ$_MEM_MC_I,AQ$_AQ$_MEM_MC_L,AQ$_AQ$_MEM_MC_S,AQ$_AQ$_MEM_MC_T,AQ$_AQ_PROP_TABLE_G,AQ$_AQ_PROP_TABLE_H,AQ$_AQ_PROP_TABLE_I,AQ$_AQ_PROP_TABLE_L,AQ$_AQ_PROP_TABLE_S,AQ$_AQ_PROP_TABLE_T,AQ$_CHANGE_LOG_QUEUE_TABLE_G,AQ$_CHANGE_LOG_QUEUE_TABLE_H,AQ$_CHANGE_LOG_QUEUE_TABLE_I,AQ$_CHANGE_LOG_QUEUE_TABLE_L,AQ$_CHANGE_LOG_QUEUE_TABLE_S,AQ$_CHANGE_LOG_QUEUE_TABLE_T,AQ$_DEQUEUE_LOG_PARTITION_MAP,AQ$_DURABLE_SUBS,AQ$_E_QUEUE_PARTITION_MAP,AQ$_INTERNET_AGENTS,AQ$_INTERNET_AGENT_PRIVS,AQ$_KEY_SHARD_MAP,AQ$_KUPC$DATAPUMP_QUETAB_1_D,AQ$_KUPC$DATAPUMP_QUETAB_1_G,AQ$_KUPC$DATAPUMP_QUETAB_1_H,AQ$_KUPC$DATAPUMP_QUETAB_1_I,AQ$_KUPC$DATAPUMP_QUETAB_1_L,AQ$_KUPC$DATAPUMP_QUETAB_1_P,AQ$_KUPC$DATAPUMP_QUETAB_1_S,AQ$_KUPC$DATAPUMP_QUETAB_1_T,AQ$_MEM_MC,AQ$_MESSAGE_TYPES,AQ$_ORA$PREPLUGIN_BACKUP_QTB_G,AQ$_ORA$PREPLUGIN_BACKUP_QTB_H,AQ$_ORA$PREPLUGIN_BACKUP_QTB_I,AQ$_ORA$PREPLUGIN_BACKUP_QTB_L,AQ$_ORA$PREPLUGIN_BACKUP_QTB_S,AQ$_ORA$PREPLUGIN_BACKUP_QTB_T,AQ$_PDB_MON_EVENT_QTABLE$_G,AQ$_PDB_MON_EVENT_QTABLE$_H,AQ$_PDB_MON_EVENT_QTABLE$_I,AQ$_PDB_MON_EVENT_QTABLE$_L,AQ$_PDB_MON_EVENT_QTABLE$_S,AQ$_PDB_MON_EVENT_QTABLE$_T,AQ$_PENDING_MESSAGES,AQ$_PROPAGATION_STATUS,AQ$_PUBLISHER,AQ$_QUEUES,AQ$_QUEUE_PARAMS,AQ$_QUEUE_PARTITION_MAP,AQ$_QUEUE_SHARDS,AQ$_QUEUE_STATISTICS,AQ$_QUEUE_TABLES,AQ$_QUEUE_TABLE_AFFINITIES,AQ$_REPLAY_INFO,AQ$_SCHEDULER$_EVENT_QTAB_G,AQ$_SCHEDULER$_EVENT_QTAB_H,AQ$_SCHEDULER$_EVENT_QTAB_I,AQ$_SCHEDULER$_EVENT_QTAB_L,AQ$_SCHEDULER$_EVENT_QTAB_S,AQ$_SCHEDULER$_EVENT_QTAB_T,AQ$_SCHEDULER$_REMDB_JOBQTAB_G,AQ$_SCHEDULER$_REMDB_JOBQTAB_H,AQ$_SCHEDULER$_REMDB_JOBQTAB_I,AQ$_SCHEDULER$_REMDB_JOBQTAB_L,AQ$_SCHEDULER$_REMDB_JOBQTAB_S,AQ$_SCHEDULER$_REMDB_JOBQTAB_T,AQ$_SCHEDULER_FILEWATCHER_QT_G,AQ$_SCHEDULER_FILEWATCHER_QT_H,AQ$_SCHEDULER_FILEWATCHER_QT_I,AQ$_SCHEDULER_FILEWATCHER_QT_L,AQ$_SCHEDULER_FILEWATCHER_QT_S,AQ$_SCHEDULER_FILEWATCHER_QT_T,AQ$_SCHEDULES,AQ$_SCHEDULES,AQ$_SHARD_MAP,AQ$_SUBSCRIBER_LWM,AQ$_SUBSCRIBER_TABLE,AQ$_SYS$SERVICE_METRICS_TAB_G,AQ$_SYS$SERVICE_METRICS_TAB_H,AQ$_SYS$SERVICE_METRICS_TAB_I,AQ$_SYS$SERVICE_METRICS_TAB_L,AQ$_SYS$SERVICE_METRICS_TAB_S,AQ$_SYS$SERVICE_METRICS_TAB_T,AQ$_WM$EVENT_QUEUE_TABLE_G,AQ$_WM$EVENT_QUEUE_TABLE_H,AQ$_WM$EVENT_QUEUE_TABLE_I,AQ$_WM$EVENT_QUEUE_TABLE_L,AQ$_WM$EVENT_QUEUE_TABLE_S,AQ$_WM$EVENT_QUEUE_TABLE_T,AQ_EVENT_TABLE,AQ_PROP_TABLE,AQ_SRVNTFN_TABLE_1,ARGUMENT$,ASSEMBLY$,ASSOCIATION$,ATEMPTAB$,ATSK$_SCHEDULE_CONTROL,ATTRCOL$,ATTRIBUTE$,ATTRIBUTE_TRANSFORMATIONS$,AUD$,AUD$UNIFIED,AUDIT$,AUDIT_ACTIONS,AUDIT_NG$,AUDIT_TRAIL,AUDTAB$TBS$FOR_EXPORT_TBL,AUD_CONTEXT$,AUD_OBJECT_OPT$,AUD_POLICY$,AUTO_RUN_DML,AUX_STATS$,AV_DUAL,AW$,AW$AWCREATE,AW$AWCREATE10G,AW$AWMD,AW$AWREPORT,AW$AWXML,AW$EXPRESS,AW_OBJ$,AW_PRG$,AW_PROP$,AW_TRACK$,BDSQL_USER_MAP,BOOTSTRAP$,BROKER_CONFIGS,BSLN_BASELINES,BSLN_METRIC_DEFAULTS,BSLN_STATISTICS,BSLN_THRESHOLD_PARAMS,BSLN_TIMEGROUPS,CACHE_STATS_0$,CACHE_STATS_1$,CAPTURED_PRIV$,CAPTURE_RUN_LOG$,CAS_CORPS_CONFIG_DETAILS,CAS_CORPS_CONFIG_DETAILS,CAS_CORPS_CONFIG_DETAILS,CAS_FORMS,CAS_FORMS,CAS_FORMS,CAS_FORMS_PACKAGES_MAPPING,CAS_FORMS_PACKAGES_MAPPING,CAS_FORMS_PACKAGES_MAPPING,CAS_PACKAGES,CAS_PACKAGES,CAS_PACKAGES,CAS_POL_SYM_REF,CAS_POL_SYM_REF,CAS_POL_SYM_REF,...(1880)
                                                                                     
    (1 rows affected)
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    edited Jun 17, 2021 12:01AM

    And if you are on newer version of SQL Server you could use (I don't have SQL Server 2017 so not tested):

    select string_agg(cast(table_name as NVARCHAR(MAX)),',') within group(order by table_name) from wkab10..SYS.ALL_TABLES
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    If you use OPENQUERY, then you could use XMLAGG:

    SELECT * FROM OPENQUERY([some_linked_server],'select rtrim(xmlcast(xmlagg(xmlelement(e, table_name || '','')) as clob),'','') from all_tables')
    

    SY.