SQL Blues

[digger1914]

First of all, a big thanks for this forum. I have been unable to get access to the Cognos one, and from what I read, I needn't bother.

I have been trying to write extracts from our ledger (Ellipse / Mincom) via TI, and am having an SQL headache. I have been able to get other queries to work, but this one, whcih seems simple, is killing me!

I have one large table (MSF900) with ALL transactiosn in the system, and it contains a field that identifies the type of transaction. There are then additional tables that contain all relevant information for the transaction (stuff like journal number, description etc).

I want a query that will allow me to select a series of transactions by parameters like account and year, and then to go away & get a few decriptive fields for each line depending on the type of transaction. My plan was to do a SELECT with the limiting criteria nd then LEFT JOIN away to get the other stuff I need. This is what I have so far, but it is giving me an "SQL command not properly ended" in the editor I am using (I grew tired of the TI lack of information)

Any SQl gurus who could give any advice out there? Any help woudl be greatly appreciated.

Thanks

David

SELECT

MSF900.DSTRCT_CODE,
MSF900.TRANSACTION_NO,
MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE,
MSF900.ACCOUNT_CODE,
MSF900.REC900_TYPE,
MSF900.TRAN_AMOUNT,
MSF900.TRAN_TYPE,
MSF900.PROCESS_DATE,
MSF900_I.DSTRCT_CODE||MSF900_I.TRANSACTION_NO||MSF900_I.USERNO||MSF900_I.REC900_TYPE,
MSF900_I.DSTRCT_CODE,
MSF900_I.SUPPLIER_NO_I,
MSF900_I.INV_NO_I,
MSF900_I.INV_ITEM_NO_I,
MSF900_I.INV_ITEM_DESC_I,
MSF900_I.WORK_ORDER_I,
MSF900_I.PROJECT_NO_I,
MSF900_I.EQUIP_NO_I

FROM

ELLIPSE.MSF900

WHERE

SUBSTR(MSF900.FULL_PERIOD,1,4) = '2009'

AND

MSF900.ACCOUNT_CODE IN ('1401', '1402', '1403', '1404',
'1405', '1406', '1407', '1458')

AND

MSF900.DSTRCT_CODE = 'SFC1'


LEFT JOIN ELLIPSE.MSF900_I

ON

MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE =
MSF900_I.DSTRCT_CODE||MSF900_I.TRANSACTION_NO||MSF900_I.USERNO||MSF900_I.REC900_TYPE

[paulsimon]

David

I don't think you mentioned which SQL database you are querying. Oracle SQL is different to DB2 to SQL Server etc. My guess is that you are querying DB2?

It may just be as simple as needing a semi colon at the end but I think you should also try the following:

Code: Select all

SELECT 
MSF900.DSTRCT_CODE,
MSF900.TRANSACTION_NO,
MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE,
MSF900.ACCOUNT_CODE,
MSF900.REC900_TYPE,
MSF900.TRAN_AMOUNT,
MSF900.TRAN_TYPE,
MSF900.PROCESS_DATE,
MSF900_I.DSTRCT_CODE||MSF900_I.TRANSACTION_NO||MSF900_I.USERNO||MSF900_I.REC900_TYPE,
MSF900_I.DSTRCT_CODE,
MSF900_I.SUPPLIER_NO_I,
MSF900_I.INV_NO_I,
MSF900_I.INV_ITEM_NO_I,
MSF900_I.INV_ITEM_DESC_I,
MSF900_I.WORK_ORDER_I,
MSF900_I.PROJECT_NO_I,
MSF900_I.EQUIP_NO_I
FROM 
ELLIPSE.MSF900 
LEFT JOIN 
ELLIPSE.MSF900_I
ON
MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE = 
MSF900_I.DSTRCT_CODE||MSF900_I.TRANSACTION_NO||MSF900_I.USERNO||MSF900_I.REC900_TYPE
WHERE
SUBSTR(MSF900.FULL_PERIOD,1,4) = '2009' 
AND
MSF900.ACCOUNT_CODE IN ('1401', '1402', '1403', '1404',
'1405', '1406', '1407', '1458')
AND
MSF900.DSTRCT_CODE = 'SFC1'

In general, you need to get all of the joins out of the way before a WHERE clause.

A few other tips:

Instead of prefixing everything with 'MSF900.' you can give this an alias eg 'FROM MSF900 M', and then just prefix with 'M', and the same for the other table, where you might want to use 'MI'.

Where you have a derived result such as

MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE,

Then you might want to give this an Alias so that TI can derive a name for the field, eg

MSF900.DSTRCT_CODE||MSF900.TRANSACTION_NO||MSF900.USERNO||MSF900.REC900_TYPE as MyField,

or whatever makes sense to describe this field.

It is also often best to convert any numeric data types that are going to be used for element names, rather than data, and also any date data types, to character so that TI can derive the correct data type. The syntax for the conversion will vary depending on the database you are querying.

Regards


Paul Simon

[digger1914]

Thanks, it's Oracle.

I will give this a go.

[digger1914]

Thanks Paul, that worked. My problem was indeed not finished the joins before using a where