ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

qcor · Jul 22nd 2019

Hi

We just started using new MRI machine today and my db and pacs log is being flooded with this error:

Code

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

it is caused by this statement (postgres):

SQL

SELECT DISTINCT COUNT(1) FROM DICOMStudies WHERE DICOMStudies.PatientNam = E'Jan^Kowalski' and DICOMStudies.PatientID = E'123456789101' and DICOMStudies.Sex = E'M' ORDER BY (DICOMStudies.PatientNam)

Any idea how to fix this?

Patryk

marcelvanherk · Jul 22nd 2019

Hi,

1) what version?

2) what operation is ongoing in the log when the error occurs?

Marcel

qcor · Jul 22nd 2019

1) 1.4.17d

2) hard to see cause there is a lot going on but I think this was the root cause (I've cut some lines cause there was a study incoming at the same time):

Code

20190722 09:36:34 UPACS THREAD 8967: STARTED AT: Mon Jul 22 09:36:34 2019
20190722 09:36:34     Calling Application Title : "MRC25702        "
20190722 09:36:34     Called Application Title : "ARPACS1         "
20190722 09:36:34     Application Context : "1.2.840.10008.3.1.1.1", PDU length: 528378
20190722 09:36:34     Presentation Context 0 "1.2.840.10008.5.1.4.1.2.1.1" 1
20190722 09:36:34 (PatientRootQuery) search level: PATIENT
20190722 09:36:36 *** ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
20190722 09:36:36 *** ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
20190722 09:36:36 *** ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

I also looked at the database log and it turns out it was the empty query (or rather a query with no filters). It was going for few hours before I caught it... was already at letter 'j'

Here is the query which started it all:

Code

2019-07-22 09:36:36 127.0.0.1(4345)LOG:  duration: 1531.999 ms  statement: SELECT DISTINCT DICOMPatients.PatientNam, DICOMPatients.PatientID, DICOMPatients.PatientSex FROM DICOMPatients WHERE DICOMPatients.PatientNam LIKE E'%' and DICOMPatients.PatientID LIKE E'%' ORDER BY (DICOMPatients.PatientNam)

As you can see - no filters... so it basically asked for ALL 180.000 patients, and then the problems started cause it started asking about each and every one of them with this query:

Quote

SELECT DISTINCT COUNT(1) FROM DICOMStudies WHERE DICOMStudies.PatientNam = E'Jan^Kowalski' and DICOMStudies.PatientID = E'123456789101' and DICOMStudies.Sex = E'M' ORDER BY (DICOMStudies.PatientNam)

producing error each time since this is not a valid query.

Putting aside the stupid part (lack of filters) this query should still return a valid answer - the number of studies the patient had. It's just that the query itself is wrong

marcelvanherk · Jul 22nd 2019

Hi,

Can you post your dicom.ini?

Marcel

qcor · Jul 22nd 2019

sure

Code

# This file contains configuration information for the DICOM server
# Do not edit unless you know what you are doing

[sscscp]
MicroPACS                = sscscp
Edition                  = Personal

# Network configuration: server name and TCP/IP port#
MyACRNema                =ARPACS1
TCPPort                  = 2009

# Reference to other files: known dicom servers; database layout; sops
ACRNemaMap               = acrnema.map
kFactorFile              = dicom.sql
SOPClassList             = dgatesop.lst

# Host(ignored), name, username and password for ODBC data source
SQLHost                  = localhost
SQLServer                =pacs
Username                 =name
Password                 =password
Postgres                 = 1
BrowseThroughDBF         = 1
DoubleBackSlashToDB      =1
UseEscapeStringConstants = 1

# Configure database
TruncateFieldNames       = 10
MaxFieldLength           = 254
MaxFileNameLength        = 255
FixPhilips               = 0
FixKodak                 = 0
KeepAlive                = 0
LargeFileSizeKB          = 4096
PrintSquareLandscape     = 0
UseKpacsDecompression    = 1
ZipTime                  = 05:
UIDPrefix                =1.2.826.0.1.3681043.2.1326
EnableReadAheadThread    = 1
PatientQuerySortOrder    =
StudyQuerySortOrder      =
SeriesQuerySortOrder     =
ImageQuerySortOrder      =
EnableComputedFields     = 1
IndexDBF                 = 1
PackDBF                  = 0
LongQueryDBF             = 1000
TCPIPTimeOut             = 300
FailHoldOff              = 60
RetryDelay               = 100
RetryForwardFailed       = 0
ImportExportDragAndDrop  = 1
QueueSize                = 128
WorkListMode             = 0
WorkListReturnsISO_IR_100 = 1
DebugLevel               = 0
Prefetcher               = 0
LRUSort                  =
AllowTruncate            =
DecompressNon16BitsJpeg  = 1
UseBuiltInJPEG           = 1
LossyQuality             = 95
IgnoreOutOfMemoryErrors  = 0
NoDICOMCheck             = 0
PadAEWithZeros           = 0
FileNameSyntax           =4

# Configuration of compression for incoming images and archival
DroppedFileCompression   = j2
IncomingCompression      = j2
ArchiveCompression       = j2

# Names of the database tables
PatientTableName         = DICOMPatients
StudyTableName           = DICOMStudies
SeriesTableName          = DICOMSeries
ImageTableName           = DICOMImages
DMarkTableName           = DICOMAccessUpdates
RegisteredMOPDeviceTable = RegisteredMOPIDs
UIDToMOPIDTable          = UIDToMOPID
UIDToCDRIDTable          = UIDToCDRID

# Banner and host for debug information
PACSName                 =ARPACS1
OperatorConsole          = 127.0.0.1

# Configure email of error messages
MailHost                 =
MailPort                 = smtp
MailSignon               =
MailFromName             =
MailRcptName1            =
MailCollectTime          = 1
MailWaitTime             = 10

# Configuration of disk(s) to store images
MAGDeviceThreshhold      = 0
MAGDeviceFullThreshHold  = 30
IgnoreMAGDeviceThreshold = 0
MAGDevices               = 1
MAGDevice0               = f:\Data\
MAGDevice1               = f:\Data1\
NightlyCleanThreshhold   = 0

# Configuration of converter programs to export DICOM slices

ForwardAssociationLevel = STUDY
ForwardAssociationCloseDelay = 5
ForwardAssociationRefreshDelay = 3600

ExportConverters =8
ExportModality0 =CT
ExportConverter0 =addfile2.exe

ExportModality1 =MR
ExportConverter1 =addfile2.exe

ExportModality2 =CR
ExportConverter2 =addfile2.exe

ExportModality3 =US
ExportConverter3 =addfile2.exe

ExportModality4 =MG
ExportConverter4 =addfile2.exe; forward compressed as j2 to AIOSA;

ExportConverter5 = ifequal "%V0032,1033", "Centrum Zdrowia w Mikolowie sp. z o.o."; forward compressed as j2 to FWDSRV;

ExportModality6 =PX
ExportConverter6 =addfile2.exe

ExportConverter7 = forward compressed as j2 to DICOMARCH;

#ExportModality7 =CT
#ExportCallingAE7 =AN_CT54058
#ExportConverter7 =forward series compressed as un to Opisy1_efilm


ImportConverters         = 4
ImportConverter0         = ifequal "%m", "SR"; destroy;

# agfy gubia 0032,1033 przy odsylaniu do pacsa. Obejscie przez mapowanie na pole 0010.1080 i z powrotem
# UWAGA! agfy zjadają pole 0010.1080 jesli ma ono więcej niz 64 znaki! (paprocany mialy ten problem)
ImportModality1		 = CR
ImportConverter1         = ifnotempty "%V0010,1080"; set 0032,1033 to "%V0010,1080";

ImportModality2		 = MG
ImportConverter2         = ifnotempty "%V0010,1080"; set 0032,1033 to "%V0010,1080";

# arietta gubi tag 0032,1033 obejscie przez mapowanie na 0010,2000 w dcm_mwl.ini i z powrotem
ImportModality3		 = US
ImportConverter3         = ifnotempty "%V0010,2000"; set 0032,1033 to "%V0010,2000";


QueryExtensions=1
CloseAssocNo=0
CloseAssocAET1=
SuppressMetaInfoNo=0
SuppressMEtaInfoAET1=

Display More

marcelvanherk · Jul 22nd 2019

Hi,

It is a bug, but if you put something in here it might be fixed:

PatientQuerySortOrder = PatientID

StudyQuerySortOrder = StudyDate

SeriesQuerySortOrder = SeriesDate

ImageQuerySortOrder = ImageNumber

Marcel

marcelvanherk · Jul 22nd 2019

Or maybe this:

PatientQuerySortOrder = 1

StudyQuerySortOrder = 1

SeriesQuerySortOrder = 1

ImageQuerySortOrder = 1

Marcel

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Participate now!

Share