asked on

What do curly brackets do in a SQL script

I had written a SQL query to extract some budget figures. I then had someone contacted me who supposedly had written the same query using Crystal Reports. The complaint was our numbers did not agree. What you see below is what was created by Crystal Reports. What is the purpose of the open and close curly brackets? This script does run using SQL. I also do not like those outer joins.

SELECT budgamnt.budgetyear,
budgamnt.department,
budgamnt.account,
budgamnt.vendorid,
budgamnt.description,
budgamnt.quantity,
budgamnt.unitcost,
budgamnt.xtndcost,
gl00100.actdescr,
gl00100.accatnum,
gl00102.accatdsc,
sy03900.txtfield
FROM
{oj (ACAA.dbo.GL00102 GL00102
FULL OUTER JOIN (ACAA.dbo.BUDGAMNT BUDGAMNT
INNER JOIN ACAA.dbo.GL00100 GL00100 ON (BUDGAMNT.ACCOUNT=GL00100.ACTNUMBR_1) AND (BUDGAMNT.DEPARTMENT=GL00100.ACTNUMBR_2)) ON GL00102.ACCATNUM=GL00100.ACCATNUM)
FULL OUTER JOIN ACAA.dbo.SY03900 SY03900 ON GL00100.NOTEINDX=SY03900.NOTEINDX}
WHERE budgamnt.budgetyear = 2021
AND NOT ( gl00100.accatnum = 31
OR gl00100.accatnum = 42
OR gl00100.accatnum = 43
OR gl00100.accatnum = 44
OR gl00100.accatnum = 45
OR gl00100.accatnum = 46 )
AND budgamnt.account = '11000'
AND budgamnt.department = '10'
ORDER BY gl00100.accatnum,
budgamnt.department,
budgamnt.account,
budgamnt.vendorid

Scott Pletcher

It's a FULL / LEFT / RIGHT OUTER JOIN escape sequence, as specified in the SQL 92 standard.

SQL doesn't require it, but does allow it.

You can get more info by Googling: sql server {oj

rwheeler23

ASKER

Very interesting, thanks. I now need to go talk to the CR person. When I run the exact same script in SQL I am getting additional records returned.

rwheeler23

ASKER

So it appears that a user sitting in the Crystal Reports GUI dragging lines and linking tables results in this
FROM
{oj (ACAA.dbo.GL00102 GL00102
FULL OUTER JOIN (ACAA.dbo.BUDGAMNT BUDGAMNT
INNER JOIN ACAA.dbo.GL00100 GL00100 ON (BUDGAMNT.ACCOUNT=GL00100.ACTNUMBR_1) AND (BUDGAMNT.DEPARTMENT=GL00100.ACTNUMBR_2)) ON GL00102.ACCATNUM=GL00100.ACCATNUM)
FULL OUTER JOIN ACAA.dbo.SY03900 SY03900 ON GL00100.NOTEINDX=SY03900.NOTEINDX}

which functionally is equivalent to this.
FROM
ACAA.dbo.BUDGAMNT BUDGAMNT
INNER JOIN ACAA.dbo.GL00100 GL00100 ON BUDGAMNT.ACCOUNT=GL00100.ACTNUMBR_1 AND BUDGAMNT.DEPARTMENT=GL00100.ACTNUMBR_2
INNER JOIN ACAA.dbo.GL00102 GL00102 ON GL00100.ACCATNUM=GL00102.ACCATNUM
FULL OUTER JOIN ACAA.dbo.SY03900 SY03900 ON GL00100.NOTEINDX=SY03900.NOTEINDX

is there any performance difference in these two approaches?

Scott Pletcher

I'm sorry, I have to bow out of this q now. Hopefully someone else will come along who can assist. Again, I'm very sorry about that.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

I came across this generated by Crtuystal Reports. Look like some old standard to make it compatible with ODBC.