concatenation 1

[DanNorris2000]

I have 2 numeric fields That I would like to combine and remove the spaces on

before

claim claimsub
3019871 1
1171 0
3019871 2

after

Claimnumber
3019871-01
1171-00
3019871-02

can anyone provide me with the proper sql to handle this?
thanks
Dan

 

[DSummZZZ]

Try this:
ALLTRIM(STR(claim)) + '-' + PADL(claimsub, 2, '0')

 

[DanNorris2000]

That worked like a champ. I would like to take it one step further. I would like this to be a conditional statement.
IF CLAIM<>0 then alltrim(str(claim))+'-'+padl(clmsub,2,0) as claimno if not I would like to leave then result field blank

3000100-00
<blank>
11125-01
11256-00

How do I work this into the program:
Open database k:\vfp98\mrcdata
Create sql view 650TYVIEW AS;
Select Ledger.pc, Ledger.uw, Contracts.ctrsts AS COMMUTED, Ledger.dev, Ledger.bookyr,;
alltrim(STR(Ledger.claim))+&quot;-&quot;+ padl(Ledger.clmsub,2,'0')AS CLMNO,;
sum(Ledger.osloss)AS OSLOS, sum(Ledger.osexp)AS osexp,;
sum(Ledger.lospd+ Ledger.lossalv+ Ledger.losded+ Ledger.lossir)AS PDLOS,;
sum(Ledger.exppd)AS PDEXP, Contracts.subcls, Ledger.class;
FROM mrcdata!Ledger LEFT OUTER JOIN mrcdata!Contracts ;
ON Ledger.uw = Contracts.uw;
AND Ledger.class = Contracts.class;
AND Ledger.ctrpoolno = Contracts.ctrpoolno;
AND Ledger.subletr = Contracts.subletr;
WHERE Ledger.pc IN (&quot;TY&quot;,&quot;OT&quot;
AND Contracts.contract NOT IN (&quot;OT 50239&quot;,&quot;OT 50399&quot;,&quot;OT 50289&quot;,;
&quot;OT 50299&quot;,&quot;OT 50239&quot;,&quot;OT 50549&quot;,&quot;OT 50279&quot;,&quot;OT 50079&quot;,&quot;OT 50229&quot;
AND Contracts.contract NOT BETWEEN &quot;OT 50999&quot; AND &quot;OT 51029Z&quot;;
AND Contracts.contract NOT BETWEEN &quot;OT 50060&quot; AND &quot;OT 50069Z&quot;;
AND Ledger.bookper <= ?'Booking Period (YYYYMM)';
AND Ledger.class <>&quot; &quot;;
GROUP BY Ledger.pc, Ledger.uw, Contracts.ctrsts, Ledger.dev,;
Ledger.bookyr, Ledger.claim, Ledger.clmsub, Contracts.subcls;
ORDER BY Ledger.pc, Ledger.uw, Contracts.ctrsts, Ledger.dev,;
Ledger.bookyr, Ledger.claim, Ledger.clmsub, Contracts.subcls
Use 650tYVIEW
Copy to k:\apps\alpha4v6\650tmty.dbf type foxplus

 

[foxdev]

[tt]Create sql view 650TYVIEW AS;
Select Ledger.pc, Ledger.uw, Contracts.ctrsts AS COMMUTED, Ledger.dev, Ledger.bookyr,;
IIF(claim<>0, alltrim(STR(Ledger.claim))+&quot;-&quot;+ padl(Ledger.clmsub,2,'0'), &quot; &quot AS CLMNO, ;
sum(Ledger.osloss)AS OSLOS, sum(Ledger.osexp)AS osexp,;
sum(Ledger.lospd+ Ledger.lossalv+ Ledger.losded+ Ledger.lossir)AS PDLOS,;
sum(Ledger.exppd)AS PDEXP, Contracts.subcls, Ledger.class;
FROM mrcdata!Ledger LEFT OUTER JOIN mrcdata!Contracts ;
ON Ledger.uw = Contracts.uw;
AND Ledger.class = Contracts.class;
AND Ledger.ctrpoolno = Contracts.ctrpoolno;
AND Ledger.subletr = Contracts.subletr;
WHERE Ledger.pc IN (&quot;TY&quot;,&quot;OT&quot;
AND Contracts.contract NOT IN (&quot;OT 50239&quot;,&quot;OT 50399&quot;,&quot;OT 50289&quot;,;
&quot;OT 50299&quot;,&quot;OT 50239&quot;,&quot;OT 50549&quot;,&quot;OT 50279&quot;,&quot;OT 50079&quot;,&quot;OT 50229&quot;
AND Contracts.contract NOT BETWEEN &quot;OT 50999&quot; AND &quot;OT 51029Z&quot;;
AND Contracts.contract NOT BETWEEN &quot;OT 50060&quot; AND &quot;OT 50069Z&quot;;
AND Ledger.bookper <= ?'Booking Period (YYYYMM)';
AND Ledger.class <>&quot; &quot;;
GROUP BY Ledger.pc, Ledger.uw, Contracts.ctrsts, Ledger.dev,;
Ledger.bookyr, Ledger.claim, Ledger.clmsub, Contracts.subcls;
ORDER BY Ledger.pc, Ledger.uw, Contracts.ctrsts, Ledger.dev,;
Ledger.bookyr, Ledger.claim, Ledger.clmsub, Contracts.subcls
[/tt]

Robert Bradley

http://www.foxdev.com/

 

[DSummZZZ]

You can use the in-line IF (IIF) statement:
.
.
.
IIF (Ledger.claim <> 0, alltrim(STR(Ledger.claim))+&quot;-&quot;+ padl(Ledger.clmsub,2,'0'), ' ') AS CLMNO,;
.
.
.
This says:
IF claim <> 0
CLMNO = ALLTRIM(....
ELSE
CLMNO = ' '
ENDIF

 

[DanNorris2000]

Open database k:\vfp98\mrcdata
Create sql view 650TYVIEW AS;
Select Ledger.pc, Ledger.uw, Contracts.ctrsts AS COMMUTED, Ledger.dev, Ledger.bookyr,;
IIF(ledger.claim<>0, alltrim(STR(Ledger.claim))+&quot;-&quot;+ padl(Ledger.clmsub,2,'0'),&quot; &quotAS CLMNO,;


The new statement returned clmno field as a character field with only a length of 1. should have come out as a minimum of 10 since claim is numeric 7 and clmsub numeric 2
without the IIF statement it came out as Character 13

 

[foxdev]

Yeah, that can happen. It's a &quot;feature&quot; of VFP; it determines the width of a column created from an expression by examining the first record. In your case, the first record it examined probably had Claim=0.

Here's how to get around it:

[tt]IIF(ledger.claim<>0, alltrim(STR(Ledger.claim))+ ;
&quot;-&quot;+ padl(Ledger.clmsub,2,'0'),space(10)) AS CLMNO,; [/tt]

Robert Bradley

http://www.foxdev.com/