I have a graph kicking off to check if a SQL Table is empty or not. If the table is empty I need the next SQL writer to start it's phase or don't run.
Is that possible?
Is that possible?
-
Yes, I see how my question is confusing.
The top 3 pieces checks the database to see if it is empty or not then writes a log statement with a timestamp to a DB table.
If the table is empty then I need for it to start the DBOutputTable (entXeia) otherwise I need it to stop.
Does that help?
Thank you for your help -
Hi, sharpiedog,
Thank you for your clarification, I think I understand now.
I suppose that select query in the first component contains count() function and therefore the result on the output is a number of records. So I would advise to add a SimpleCopy component after this first DBInputTable. One stream would continue to the Reformat and DBoutputTable as it does now and in the second would be a ExtFilter with condition like "$in.0.count == 0". This filter would ensure that if something else than 0 comes to the filter, graph run will end in this point. Otherwise, components in the phase 2 will be executed. The first output port of the filter would be connected Reformat. Reformat would serve just as a generator of the query for your second DBInputTable (entXeia) as it would send the whole query via the edge to the DBInputTable. Query URL in DBInputTable would look like port:$0.field1:discrete.
I hope you understand my idea. If you have any questions, please, let me know.
Best regards, -
I am attaching the updates I have made.
I have also included some of the additional information to the Graph. I am now assuming I can't have Query URL & SQL Query in the same DBInputTable, is that correct?
Thank Andy -
I think I have it corrected. I added the following SQL to the top of my orginal code & that seems to be working on my graph now. Thank you for the inspiration.
Added to:
DECLARE @Truncated varchar(40),@Error varchar(40)
SET @Truncated = 'Database is Clean For Data Load'
SET @Error = 'Database Not Truncated'
IF (SELECT COUNT(*) FROM CQInitiate.dbo.CQARK_Patient) > 0
SELECT @Error
ELSE
DBInputTable Kicks off -
Hi, Andy,
You can set either SQL query or Query URL but not both at the same time. Purpose of both is basically the same - DBInputTable needs some query and there are two ways how to set it.
A) By setting the query directly as a string into SQL query field
B) By setting the URL to some file or object containing the string with the query into Query URL field
In any way, you should not set in Query URL some condition as you did on your screenshot.
Regarding your filter expression, I do not know how your first query looks like so it may or may not be correct. You mentioned that your graph is working now so I assume it was correct.
I have also noticed that you have your filter connected to Reformat and then Reformat to UniversalDataWriter. But my original idea was to connect ExtFilter to Reformat and then Reformat directly to the second DBInputTable (entXeia). But if you figured it out in some other way, good for you.
Anyway, I am glad you made your graph working and good luck with your next CloverETL projects.
Best regards, -
Lubos,
I want to thank you again for helping me figure out & learn what I needed to do. It was a great feeling to see it work & inspired me to work on more ideas.
Essentially what I did was re-write the query in the entXeia... DBInput node (now that I know the difference between the query & URL) :
It looked something like this:
DECLARE @Truncated varchar(40),@Error varchar(40)
SET @Truncated = 'Database is Clean For Data Load'
SET @Error = 'Database Not Truncated'
IF (SELECT COUNT(*) FROM <db_name>.dbo.<table_name>) > 0
SELECT @Error
ELSE
SELECT DECLARE @Truncated varchar(40),@Error varchar(40)
SET @Truncated = 'Database is Clean For Data Load'
SET @Error = 'Database Not Truncated'
IF (SELECT COUNT(*) FROM CQInitiate.dbo.CQARK_Patient) > 0
SELECT @Error
ELSE
select entrecno
from...............................
Please sign in to leave a comment.
Comments 8