Cheque formula

[Former Member]

Trying to create a query which looks at the following tables:

• Deposit - ODPS

• Deposit lines - OCHH

• Payment means - RCT1

This query is aimed at providing a list of cheque details per deposit slip number, which includes specific UDF's which are sitting in the payment means/ cheque screen.

I would like the following information:

ODPS - Deposit

• Payment number

• Payment date

Where payment type = 'K' = cheque

Where payment number = [0%]

OCHH - deposit lines

• Cheque date

• Cheque number

• Branch

• A/C number

• Cheque amount

RCT1

• Amount

• UDF1

• UDF2

When I bring the tables together, the lines duplicate.

I would greatly appreciate some assistance please.

[Former Member]

For those of you who have been watching this thread closely.

Below is the Final formula!

SELECT DISTINCT T0.[DeposNum], T0.[DeposDate], T1.[DueDate], T2.[DocNum], T2.[CardName], T1.[U_Cheque_name], T1.[Branch], T1.[AcctNum], T1.[CheckSum] FROM ODPS T0 , RCT1 T1 INNER JOIN ORCT T2 ON T1.DocNum = T2.DocEntry WHERE T0.DeposType ='K' AND T0.[DeposDate] =[%0]

A big thank you to those how have assisted with this.

[Former Member]

Gordon - unable to execute 2nd query - due to error in formula.

[Former Member]

Sorry. It should be T2.U_X10_chename. Not T1.

[Former Member]

free text name = X10_chename

Cardcode name = X10_chenam

thanks

[Former Member]

Please check these two queries:

SELECT DISTINCT T0.DeposDate, T0.DeposNum, 
T1.CheckDate, T1.CheckKey, T1.CheckNum, 
T1.Branch, T1.AcctNum, T1.CheckSum, T3.CardName
FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = 
T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
INNER JOIN OCRD T3 ON T3.CardCode = T1.CardCode
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

and

SELECT DISTINCT T0.DeposDate, T0.DeposNum, 
T1.CheckDate, T1.CheckKey, T1.CheckNum, 
T1.Branch, T1.AcctNum, T1.CheckSum, T2.X10_chename,
T3.CardName
FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
INNER JOIN OCRD T3 ON T3.CardCode = T1.CardCode
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

If the second one has duplicates, it means your free text will be no direct link to the check.

[Former Member]

Hi Suda,

I am unable to get the last formaula to work.

Can you please advise where I am to enter UDF1 (text) and UDF 2 (FMS) in the formala.

The 2nd formaula above is not pulling through the text infromation - the fields are blank.

[former_member583013]

What is the actual name of the 2 UDF's

one which is of type FREE TEXT and the other which has the CardCode.

[Former Member]

Hi Suda,

Formula 1 = 2 lines (correct infromation)

Formula 2 = 2 lines - FMS colum appears, Text column is blank (no hading name and no data in field).

[former_member583013]

Does it mean the second query is working correctly. I have added the heading Text. You may change it to something relevant.

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, CASE WHEN T2.U_X10_chename = '' THEN '-' END [Text],T2.U_X10_chenam FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

[Former Member]

This Free Text has to have one on one relationship with each check number. Otherwise, the duplication is not avoidable.

Thanks,

Gordon

[Former Member]

Hi Gordon,

Cheque line items still duplicating in the report. I hope the information I have outlined below explains the requirements.

I have two UDF's in the incoming payment Cheque screen.

1. one is free text (which may or may not be entered)

2. is a FMS which is $[ORCT.cardname).

Formula used:

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename,T2.U_X10_chenam FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs

INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum

WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

Current results

#Date,Pay No. Cheque Date Cheque Key Cheque Num Line UDF(free text) UDF (FMS

$[ORCT.Cardname)

1 08.10.08 9 08.10.08 16 1 10 ATESTA Customer A

2 08.10.08 9 08.10.08 16 1 10 BTESTB Customer A

3 08.10.08 9 08.10.08 17 2 20 ATESTA Customer A

4. 08.10.08 9 08.10.08 17 2 20 BTESTB Customer A

Line 2 and 3 are NOT correct (as you can see the cheque number is duplicated).

What results should look like

#Date,Pay No. Cheque Date Cheque Key Cheque Num Line UDF name UDF (FMS

$[ORCT.Cardname)

1 08.10.08 9 08.10.08 16 1 10 ATESTA Customer A

2. 08.10.08 9 08.10.08 17 2 20 BTESTB Customer A

[former_member583013]

Lisa,

Could you check what your results are when you try this..without the UDF's and could you paste the results

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum
FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

Also check this

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, CASE WHEN T2.U_X10_chename = '' THEN '-' END,T2.U_X10_chenam FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

[Former Member]

Hi Gordon, I am unable to run the query - an error message occurs.

Can you please advise a new query to run please?

[Former Member]

Check with this:

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs
INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

Thanks,

Gordon

[Former Member]

Gordon, I am getting the same error?!?

[Former Member]

What is the difference between two "duplicated" records? Are all differences the same to all the records.

[Former Member]

What I means is like this:

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.RcptNum, 
T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, 
T1.AcctNum, T1.CheckSum, T2.U_X10_chename
 FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = 
T1.DpstAbs INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum 
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

To see if you got duplicate or not one UDF at a time.

Thanks,

Gordon

[Former Member]

says - Conversion failed when converting datetime from character string.

[Former Member]

What is your UDF property? Are they text? If not, you can not trim them.

You can leave one UDF to try this query without formula to see if your got two records.

[Former Member]

Hi Gordon,

Both fields are text fields.

Can you please specify the query please.

[Former Member]

Gordon, there is an error in the query.

[Former Member]

Hi Gordon,

Your point appears logical - as this is related to the two UDF's which are text.

Any chance of changing the formula to accommodate this?

[former_member583013]

Lisa,

I would suggest that you try to concatenate the 2 UDF's

LTRIM(RTRIM(T2.U_X10_chename)) + ' ' + LTRIM(RTRIM(T2.U_X10_chename2))

[Former Member]

Can you please specif the full formula please.

[former_member583013]

What are you referring as Full Formula?

Is it the whole Query...

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.RcptNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch,

T1.AcctNum, T1.CheckSum, LTRIM(RTRIM(T2.U_X10_chename)) + ' ' + LTRIM(RTRIM(T2.U_X10_chename2))

FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs

INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum

WHERE T0.DeposType ='K' and T0.DeposDate = '[%0\]'

[Former Member]

Here you are:

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.RcptNum, 
T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, 
T1.AcctNum, T1.CheckSum, LTRIM(RTRIM(T2.U_X10_chename)) + 
' ' + LTRIM(RTRIM(T2.U_X10_chename2))
 FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = 
T1.DpstAbs INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum 
WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

The time sequence here is confusing. I would not post the same query if the above already post.

Anyway, if your result is still more than one line, you have to update your UDF value to get it right.

Thanks,

Gordon

[Former Member]

Hi Suda,

This is not an/the issue - have changed below.

What is happening in two ROW's are being created in the table - with the same information.

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.RcptNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename, T2.U_X10_chename2 FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum WHERE T0.DeposType ='K' and T0.DeposDate = '%0'

[former_member583013]

It will be hard to tell unless I can see it...

[Former Member]

Hi Suda,

Sample below..

# Payment Date Payment No. Cheque Date Cheque Key Cheque Number Branch Number Account Number Cheque Amount

196 01.10.08 5 30.09.08 190 1234 902 808 57.1

197 01.10.08 5 30.09.08 190 1234 902 808 57.1

198 01.10.08 5 30.09.08 191 2222 93 3 43.2

199 01.10.08 5 30.09.08 191 2222 93 3 43.2

[Former Member]

Hi Lisa,

When you use distinct as the first restriction, it will be not possible to have duplicate lines. Please check where are the differences between the same line. I think it probably come out of your UDF.

Thanks,

Gordon

[Former Member]

Hi Suda,

The formula is almost there -thank you for you help so far.

There are several lines which are duplicating in the query - where 2 cheques are processed in the one incoming payment - both of these cheques are appearing twice in the query.

Is there any way we can eliminate this please?

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.RcptNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename, T2.U_X10_chename FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum WHERE T0.DeposType ='K' and T0.DeposDate = '[%0]'

[former_member583013]

I am not able to reproduce the exact scenario as I am not seeing any duplication when I tried..

Also I notice you have the same User field twice on the SQL

T2.U_X10_chename, T2.U_X10_chename

[Former Member]

-

[Former Member]

I tried below - but have the same results. I am probably missing a link between the Deposit and Incoming payments tables - and ideas?

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename, T2.U_X10_chename FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs, RCT1 T2 WHERE T0.DeposType ='K' and T0.DeposDate =%0

[former_member583013]

Check this

SELECT DISTINCT T0.DeposDate, T0.DeposNum, T1.CheckDate, T1.CheckKey, T1.CheckNum, T1.Branch, T1.AcctNum, T1.CheckSum, T2.U_X10_chename, T2.U_X10_chename FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs

INNER JOIN RCT1 T2 ON T2.DocNum = T1.RcptNum

WHERE T0.DeposType ='K' and T0.DeposDate = '[%0\]'

[Former Member]

Thanks Suda,

SELECT T0.[DeposDate], T0.[DeposNum], T1.[CheckDate], T1.[CheckKey], T1.[CheckNum], T1.[Branch], T1.[AcctNum], T1.[CheckSum], T2.[U_X10_chename], T2.[U_X10_chename] FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs, RCT1 T2 WHERE T0.[DeposType] ='K' and T0.[DeposDate] =[%0]

[former_member583013]

Could you try to use SELECT DISTINCT ...........

[former_member583013]

Lisa,

Could you please post your SQL Query