SQL Command Limit 2

[fmrock]

Is there a limit on the size that a SQL command can be. I have two SQL queries that I am using a UNION ALL to join together. Right now its at 100 lines. Each side of the query works fine, but when I put them together it looks as if its only running part of it.

I cant create a view/stored procedure because this is running against a vendors ORACLE database.

Thanks for your help

 

[briangriffin]

Try pasting the code from Database: View SQL into an editor and see what results you get.

If you're connecting natively try running it through an ODBC connection, if you're using ODBC try native and see what changes, if anything.

 

[fmrock]

I am using the Oracle connection. It works when I run the query through Oracles SQL+, but not in Crystal.

I am not sure If it has a line length limit or a character length limit.

 

[lbass]

So as Brian suggested, does the entire query appear in database->show SQL query? I think there might be a limit to the number of lines, but 100 doesn't seem that great. Why not also post the query here?

-LB

 

[fmrock]

Here is the query...

Both sides of the union work fine.

The second part is just creating a summary of the 3 locations that are returned in the first query.

SELECT T1.Group_Num "Site_Number"
	,T1.Site_Name "Site_Name"
	,T1.dtMonth "Month"
	,T1.Gross_Charges "Gross_Charges"
	,T1.Billed_Visits "Billed_Visits"
	,T1.Gross_Payments "Cash_Receipts"
	,T1.Contractual_Allowances "Contractual_Allowances"
	,T1.Self_Pay_Bad_Debt_Transfers "Self_Pay_Bad_Debt_Transfers"
	,T1.Third_Party_Denial_Write_offs "Third_Party_Denial_Write_offs"
--	,T1.Gross_Adjustments "Gross_Adjustments"
	,SUM(T1.Gross_Payments) OVER (PARTITION BY T1.Group_Num,T1.Site_Name ORDER BY T1.Group_Num,T1.dtMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) "Sum_Payments_Last_3_Months"
	,SUM(T1.Monthly_Balance) OVER (PARTITION BY T1.Group_Num,T1.Site_Name ORDER BY T1.Group_Num,T1.dtMonth) "Active_AR_Balance"
	,T1.Monthly_Balance "Monthly_Balance"
	--,T1.Active_AR_Balance "Active_AR_Balance"
	,add_months(trunc(sysdate,'MONTH'),-12) "First_Month"
	--,to_date(to_char(add_months(sysdate,-4),'YYYY') || '0401', 'yyyymmdd') "First_Month"
	,(add_months(T1.dtMonth, 1)-1) - add_months(T1.dtMonth, -2) "NumDays_Last3months"
FROM
(SELECT 
	C.CPCODE Group_Num, L.LOCNAME Site_Name
	,trunc(C.POSTDATE,'MONTH') dtMonth
	,SUM(CASE WHEN (C.TYPE='C') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Charges
	,SUM(CASE WHEN (C.TYPE='P') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Payments	
	--,SUM(CASE WHEN (C.TYPE='A') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Adjustments
	,SUM(C.AMOUNT) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Monthly_Balance
	,COUNT (DISTINCT (CASE WHEN ((C.TYPE='C') AND (C.PRCODE IN ('99281','99282','99283','99284','99285','99291'))) THEN C.CPCODE || C.ACCOUNT END)) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Billed_Visits
	,SUM(CASE WHEN ((C.TYPE='P') AND (C.ALLOWED IS NOT NULL)) THEN C.ALLOWED ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Contractual_Allowances
	,SUM(CASE WHEN ((C.TYPE='A') AND (C.PRCODE='75')) THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Self_Pay_Bad_Debt_Transfers
	,SUM(CASE WHEN ((C.TYPE='A') AND (C.PRCODE IN ('42','43','43Q','44','44A','44B','44C','44D','44E','44F','44G','44H','44I','44J','44K','44L','44M','44N','44O','44P','44Q','44R','44S','44T','44U','44V','44W','44X','44Y','44Z'))) THEN C.AMOUNT ELSE 0 END)  OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Third_Party_Denial_Write_offs
	,row_number() OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH') order by C.rowid) rn
FROM 
	(SELECT CPCODE, ACCOUNT, DOCLOC, TYPE, PRCODE, AMOUNT, SPLITFLAG, ALLOWED
		, CASE WHEN (BATCHNBR='888888') THEN to_date(POSTDATE,'j') ELSE NVL(to_date(ORIGPOSTDATE,'j'),to_date(POSTDATE,'j'))END POSTDATE
		FROM MEDCHARGES
		WHERE (CPCODE IN ('1','2','3'))) C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
WHERE
	(C.CPCODE IN ('1','2','3'))
	AND (C.POSTDATE IS NOT NULL)
	AND (C.POSTDATE <= trunc(sysdate,'MONTH')-1)
) T1 WHERE (T1.rn=1)
/*------------------------------*/
UNION ALL
/*------------------------------*/	
SELECT
	'ALL' "Site_Number",'Consolidated' "Site_Name", T2.dtMonth "Month"
	, SUM(T2.Gross_Charges) "Gross_Charges"
	, SUM(T2.Billed_Visits) "Billed_Visits"
	, SUM(T2.Cash_Receipts) "Cash_Receipts"
	, SUM(T2.Contractual_Allowances) "Contractual_Allowances"
	, SUM(T2.Self_Pay_Bad_Debt_Transfers) "Self_Pay_Bad_Debt_Transfers"
	, SUM(T2.Third_Party_Denial_Write_offs) "Third_Party_Denial_Write_offs"
	, SUM(T2.Sum_Payments_Last_3_Months) "Sum_Payments_Last_3_Months"
	, SUM(T2.Active_AR_Balance) "Active_AR_Balance"
	, SUM(T2.Monthly_Balance) "Monthly_Balance"
	, MIN (T2.NumDays_Last3months) "NumDays_Last3months"
	, MIN (T2.First_Month) "First_Month"
FROM 
(SELECT
	T1.Group_Num Site_Number, T1.Site_Name Site_Name,T1.dtMonth
	,T1.Gross_Charges Gross_Charges,T1.Billed_Visits Billed_Visits, T1.Gross_Payments Cash_Receipts
	,T1.Contractual_Allowances Contractual_Allowances
	,T1.Self_Pay_Bad_Debt_Transfers Self_Pay_Bad_Debt_Transfers
	,T1.Third_Party_Denial_Write_offs Third_Party_Denial_Write_offs
--	,T1.Gross_Adjustments Gross_Adjustments
	,SUM(T1.Gross_Payments) OVER (PARTITION BY T1.Group_Num,T1.Site_Name ORDER BY T1.Group_Num,T1.dtMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) Sum_Payments_Last_3_Months
	,SUM(T1.Monthly_Balance) OVER (PARTITION BY T1.Group_Num,T1.Site_Name ORDER BY T1.Group_Num,T1.dtMonth) Active_AR_Balance
	,T1.Monthly_Balance Monthly_Balance
	--,T1.Active_AR_Balance Active_AR_Balance
	,add_months(trunc(sysdate,'MONTH'),-12) First_Month
	--,to_date(to_char(add_months(sysdate,-4),'YYYY') || '0401', 'yyyymmdd') First_Month
	,(add_months(T1.dtMonth, 1)-1) - add_months(T1.dtMonth, -2) NumDays_Last3months
FROM
(SELECT 
	C.CPCODE Group_Num
	, L.LOCNAME Site_Name
	,trunc(C.POSTDATE,'MONTH') dtMonth
	,SUM(CASE WHEN (C.TYPE='C') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Charges
	,SUM(CASE WHEN (C.TYPE='P') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Payments	
	--,SUM(CASE WHEN (C.TYPE='A') THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Gross_Adjustments
	,SUM(C.AMOUNT) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Monthly_Balance
	,COUNT (DISTINCT (CASE WHEN ((C.TYPE='C') AND (C.PRCODE IN ('99281','99282','99283','99284','99285','99291'))) THEN C.CPCODE || C.ACCOUNT END)) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Billed_Visits
	,SUM(CASE WHEN ((C.TYPE='P') AND (C.ALLOWED IS NOT NULL)) THEN C.ALLOWED ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Contractual_Allowances
	,SUM(CASE WHEN ((C.TYPE='A') AND (C.PRCODE='75')) THEN C.AMOUNT ELSE 0 END) OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Self_Pay_Bad_Debt_Transfers
	,SUM(CASE WHEN ((C.TYPE='A') AND (C.PRCODE IN ('42','43'))) THEN C.AMOUNT ELSE 0 END)  OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH')) Third_Party_Denial_Write_offs
	,row_number() OVER (partition by C.CPCODE,L.LOCNAME,trunc(C.POSTDATE,'MONTH') order by C.rowid) rn
FROM 
	(SELECT CPCODE, ACCOUNT, DOCLOC, TYPE, PRCODE, AMOUNT, SPLITFLAG, ALLOWED
		, CASE WHEN (BATCHNBR='888888') THEN to_date(POSTDATE,'j') ELSE NVL(to_date(ORIGPOSTDATE,'j'),to_date(POSTDATE,'j'))END POSTDATE
		FROM MEDCHARGES WHERE (CPCODE IN ('1','2','3'))) C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
WHERE
	(C.CPCODE IN ('1','2','3'))
	AND (C.POSTDATE IS NOT NULL)
	AND (C.POSTDATE <= trunc(sysdate,'MONTH')-1)
) T1
WHERE (T1.rn=1)) T2 GROUP BY T2.dtMonth ORDER BY T2.dtMonth	




	--AND (C.SPLITFLAG IS NULL)
	--AND (C.POSTDATE <= to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))	
	--AND (C.POSTDATE between to_Number(to_char(add_months(trunc(sysdate,'MONTH'),-12),'J')) and to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))

 

[lbass]

Is this from Database->show SQl query? Does it show the entire query when compared to your original query?

-LB

 

[fmrock]

No, Crystal will not get that far. When you are at Database >>Database Expert and then Add your command and click ok. It gets an error right way. If you only put in one side of the union it works fine. Once I union these 2 together, I get the error.

I can run the query together through SQL+ with no issue.

This all kind of leads me to believe there is a limit on the size of a command.

 

[lbass]

What is the error message?

-LB

 

[TeyBrady]

There is a limit to what you can put in a command object and you are not close to the limit. When you get to the limit the editor will not take any more characters.

I think your problem is the order by statement. When you are doing a union or union all the order by is for all of the SQL. Yours is specific to the second statement with the t2 field. Try removing it. If you want all of the first records before all of the second records you can select a 1 in the first statement and a 2 in the second and sort on that and then sort on your field but you can't reference it as t2. I think this will all make sense when you realize that you cannot sort the upper or lower SQL statements.

The other thing that only looked odd was I don't think you have your fields in the same order. I only looked at the last two things you are selecting but they are not in the same order. first_month is the second to last field in the first statement and last statement in the second. However the SQL will not care what the names are. It will let you mess everything up as long as you don't mess with the types. The name of the fields in the second statement in the union are ignored. They just line up with the names from the first statement and take those names.

 

[fmrock]

Thanks Tey, the order by and the last 2 fields in the second query was the issue.

 

[TeyBrady]

Double win! I'm glad it helped.

 

[lbass]

It was great that you picked that out, Tey. Just a comment. You can add an order by clause at the end of the entire query (after the last unioned statement), if you reference the fields by ordinal number in the select clause, e.g.:

Order by 1,3

...to order by the first and third fields.

-LB

 

[fmrock]

I really appreciate both of you looking at this. I was able to adjust the order in my Crystal report easy enough.

Also a second set of eyes is always helpful as I have been looking at this awful query for way to long this week.

 

[TeyBrady]

You can do things like this too. I have forced all the records from the first query to sort before the second query and I also sorted only the second query by deptid. I also renamed the full_name field to foo and used foo as the sort field.

select 1 qry, emplid, full_name foo, ' ' dept, deptid from person
union all
select 2 qry, emplid, full_name, deptid dept, deptid from person
order by qry, dept, foo

The qry field I added with the 1 and 2 can be helpful when debugging when you are wondering what part of the union returned a record.

Fun stuff...